Below is a sample of rows and columns of data in which I am trying to write a nested “if statement” that would calculate the number of service months based on the following conditions:
1. Employee’s birthdate must occur before they are eligible to enroll
2. The date eligible to enroll must occur either before or on 12/31/2013
3. There date of rehire must occur either on or before 12/31/2013
4. The period of time between their rehire date and there initial termination date must be = or less than 365 days.
However, if the employee was already eligible as indicated in Column AB the value that would need to be returned would be “Eligible”, and no months of service needs to be calculated.
Column N
Date TM Attains Age 19
Row 1 6/1/2014
Row 2 4/1/2011
Column Z
Date Eligible to Enroll
Row 1: 10/1/2014
Row 2: 4/1/2013
Column X
Hire Date
Row 1: 12/9/2013
Row 2: 6/8/2012
Column AA
Initial Termination Date
Row 1: 12/26/2013
Row 2: 8/3/2012
Column AB
1st Hire Eligibility
Row 1: Eligible
Row 2: Not Eligible
Column AD
Rehire Date
Row 1: 1/14/2015
Row 2: 7/20/2013
I have written the following formula, which would calculate the number of months of service or return a value of “Not Eligible”, but if the employee was already eligible as indicated in Column AB none of these additional calculations would be required, but don’t know how to write a formula that would first look for the value in Column AB and then if AB shows Not Eligible then calculate the number of months based on the above conditions being met or return a value of Not Eligible.
=IF(AND($Z5136>=$N5136,Z5136<=DATE(2013,12,31),AD5136<=DATE(2013,12,31),(AD5136-AA5136)<=365,AB5136="Not Eligible"),DATEDIF(X5136,AD5136,"m"),"Not Eligible")
Any help would be greatly appreciated.
1. Employee’s birthdate must occur before they are eligible to enroll
2. The date eligible to enroll must occur either before or on 12/31/2013
3. There date of rehire must occur either on or before 12/31/2013
4. The period of time between their rehire date and there initial termination date must be = or less than 365 days.
However, if the employee was already eligible as indicated in Column AB the value that would need to be returned would be “Eligible”, and no months of service needs to be calculated.
Column N
Date TM Attains Age 19
Row 1 6/1/2014
Row 2 4/1/2011
Column Z
Date Eligible to Enroll
Row 1: 10/1/2014
Row 2: 4/1/2013
Column X
Hire Date
Row 1: 12/9/2013
Row 2: 6/8/2012
Column AA
Initial Termination Date
Row 1: 12/26/2013
Row 2: 8/3/2012
Column AB
1st Hire Eligibility
Row 1: Eligible
Row 2: Not Eligible
Column AD
Rehire Date
Row 1: 1/14/2015
Row 2: 7/20/2013
I have written the following formula, which would calculate the number of months of service or return a value of “Not Eligible”, but if the employee was already eligible as indicated in Column AB none of these additional calculations would be required, but don’t know how to write a formula that would first look for the value in Column AB and then if AB shows Not Eligible then calculate the number of months based on the above conditions being met or return a value of Not Eligible.
=IF(AND($Z5136>=$N5136,Z5136<=DATE(2013,12,31),AD5136<=DATE(2013,12,31),(AD5136-AA5136)<=365,AB5136="Not Eligible"),DATEDIF(X5136,AD5136,"m"),"Not Eligible")
Any help would be greatly appreciated.
