Spreadsheet: 553027d1513889549-determining-a-status-from-a-date-range-example
Sheet: Hardcode

SSN | First Name | Last Name | BC | [NAME] On 1 | Offer Ends On 1 | Enrollment Starts On 1 | Enrollment Ends On 1 | Monthly Premium 1 | BC2 | [NAME] On 2 | Offer Ends On 2 | Enrollment Starts On 2 | Enrollment Ends On 2 | Monthly Premium 2 | BC3 | [NAME] On 3 | Offer Ends On 3 | Enrollment Starts On 3 | Enrollment Ends On 3 | Monthly Premium 3 | BC4 | [NAME] On 4 | Offer Ends On 4 | Enrollment Starts On 4 | Enrollment Ends On 4 | Monthly Premium 4 | BC5 | [NAME] On 5 | Offer Ends On 5 | Enrollment Starts On 5 | Enrollment Ends On 5 | Monthly Premium 5 | BC6 | [NAME] On 6 | Offer Ends On 6 | Enrollment Starts On 6 | Enrollment Ends On 6 | Monthly Premium 6 | Hire Date | Birthdate | TERM? | OFFERS |  | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC
1 | LONNIE | BROWN | 4 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-09-10 00:00:00 | 82.34 | 7 | 2016-09-11 00:00:00 | 2016-12-31 00:00:00 | 2016-09-11 00:00:00 | 2016-12-31 00:00:00 | 99.67 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 05/05/1998 | 12/25/1958 |  |  |  | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2
2 | SHAWN | BLACK | 7 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-06-19 00:00:00 | 99.67 | 4 | 2016-06-20 00:00:00 | 2016-12-31 00:00:00 | 2016-06-20 00:00:00 | 2016-12-31 00:00:00 | 82.34 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME] | 03/19/1984 |  |  |  | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2
3 | ADAM | GREEN | 6 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-09-04 00:00:00 | 99.67 | 4 | 2016-09-05 00:00:00 | 2016-12-31 00:00:00 | 2016-09-05 00:00:00 | 2016-12-31 00:00:00 | 82.34 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 03/19/2012 | 01/06/1976 |  |  |  | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2
4 | JERRIANN | RED | 4 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-10-06 00:00:00 | 82.34 | 6 | 2016-10-07 00:00:00 | 2016-12-31 00:00:00 | 2016-10-07 00:00:00 | 2016-12-31 00:00:00 | 99.67 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 03/12/2010 | 07/28/1993 |  |  |  | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 2 | OFFER 2 | OFFER 2
5 | FRANK | YELLOW | 4 | 2016-01-01 00:00:00 | 2016-02-01 00:00:00 | 2016-01-01 00:00:00 | 2016-02-01 00:00:00 | 82.34 | 4 | 2016-02-02 00:00:00 | 2016-12-31 00:00:00 | 2016-02-02 00:00:00 | 2016-12-31 00:00:00 | 502.88 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 05/17/1976 | 03/27/1956 |  |  |  | OFFER 1 | OFFER 1 | TERM | TERM | TERM | TERM | TERM | TERM | TERM | TERM | TERM | TERM
6 | MARGARET | PURPLE | 8 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 82.34 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 06/29/1976 | 03/05/1959 |  |  |  | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1
7 | [NAME] | BLUE | 4 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 |  |  | 82.34 | 4 | 2016-06-01 00:00:00 | 2016-12-31 00:00:00 | 2016-06-01 00:00:00 | 2016-12-31 00:00:00 | 82.34 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 12/08/2006 | 05/12/1990 |  |  |  | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2 | OFFER 2
8 | MOSES | PINK | 6 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 |  |  | 99.67 | 6 | 2016-08-01 00:00:00 | 2016-12-31 00:00:00 | 2016-08-01 00:00:00 | 2016-08-31 00:00:00 | 99.67 | 6 | 2016-09-01 00:00:00 | 2016-12-31 00:00:00 | 2016-09-01 00:00:00 | 2016-09-29 00:00:00 | 99.67 | 6 | 2016-09-30 00:00:00 | 2016-12-31 00:00:00 |  |  | 99.67 |  |  |  |  |  |  |  |  |  |  |  |  | 05/24/2014 | [NAME] |  |  |  | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 1 | OFFER 2 | OFFER 4 | OFFER 4 | OFFER 4 | OFFER 4