I have data that shows the dates that an employee got hired. Employees are eligible for insurance for the 1st month following 60 days of continuous employment (we do have employees who get terminated and rehired, so multiple term and rehire dates). My goal is to create a formula that will look at the dates and if the person is hired and in their eligibility period put a certain code, but once they have passed their eligibility period, then switch to a different code. I have been trying a few different nested IF, AND, and OR functions, but I can't seem to find what is quite right. Especially when you consider that an employee could be hired on 1/1/2015, then terminated on 2/3/15, rehired on 6/5/15 and then termed again on 12/20/15.
The result of this example would be that I would want EXCEL to give me the following:
From 1/1/2015 - 2/3/15 - Code A (hired and in eligibility period)
From 2/3/15 - 6/5/15 - Code B (not employed)
From 6/5/15 - 8/30/15 - Code A (hired and in eligibility period)
From 9/1/15 - 12/19/15 - Code C (eligible)
From 12-20/15 onward - Code B (not employed)
Any help or ideas on this would be wonderful. I'm using Excel 2010.