I am writing an if statement to determine eligibility based on certain age and date criteria being met. I have the following data:
Year Being Tested is 2013
Column A1 Date of Eligibility based on age is 1/1/2010
Column B1 Date of Eligibility for Enrollment is 9/1/2008
Column C1 Termination Date is 4/27/2011 or could be blank if person remains employed.
The current formula I have written to determine eligibility is the following:
=IF(AND(OR(C1="",C1>B1),A1<B1,B1<=DATE(2012,12,31)),"Eligible","Not Eligible")
With this formula it is returning a Not Eligible because my Age Eligibility Date is greater than the Enrollment Date, but because the person remains employed until 4/27/2011 they are eligible. Each person has various dates and not certain how to write the formula whereby the termination date is also considered if the Age Eligibility Date goes past the date for enrollment eligibility.
In this example if their eligibility age occurs after their enrollment eligibility date they would not be eligible unless their termination date is equal to or less than the date in which they attain the required age eligibility date. In some cases there will not be a termination date because the individual is still employed so it is left blank.
Any assistance would be greatly appreciated.
Year Being Tested is 2013
Column A1 Date of Eligibility based on age is 1/1/2010
Column B1 Date of Eligibility for Enrollment is 9/1/2008
Column C1 Termination Date is 4/27/2011 or could be blank if person remains employed.
The current formula I have written to determine eligibility is the following:
=IF(AND(OR(C1="",C1>B1),A1<B1,B1<=DATE(2012,12,31)),"Eligible","Not Eligible")
With this formula it is returning a Not Eligible because my Age Eligibility Date is greater than the Enrollment Date, but because the person remains employed until 4/27/2011 they are eligible. Each person has various dates and not certain how to write the formula whereby the termination date is also considered if the Age Eligibility Date goes past the date for enrollment eligibility.
In this example if their eligibility age occurs after their enrollment eligibility date they would not be eligible unless their termination date is equal to or less than the date in which they attain the required age eligibility date. In some cases there will not be a termination date because the individual is still employed so it is left blank.
Any assistance would be greatly appreciated.
