Please help with date logic problem

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,


I would truly be most greatful for help to calculate a potential cash outlay for my employees depandants schools books using three date variables as reference points.


Using today as a basis for the logic tests: if I have an employee who has more than 5 years of tenure, and their dependant is more than 5 years old but less than 18, or the employee will gain 5 years of tenure between now and the dependants' 18th birthday = how many birthdays will the dependant have between employee reaching tenure and their 18th birthday?

Please find for your reveiw and referance this information:

Cell B11 is Employee hire date in MM/DD/YY. Cell C11 is the logical return of DATEIF from Today in Years, Months, Days of the employees tenure. Cell E11 is the Dependants DOB in MM/DD/YY. Cell G11 is the logical return of DATEIF from Today in Years, Months, Days of the dependants' age. Cell H11 is the Dependants date of 18th Birthday in MM/DD/YY format.

Any help in working this problem will be most appreciated and welcome.

I humbly thank you in advance for taking the time to read my problem and helping me find a solution.

Thank you,

Criz

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
This should do it

E11 : dependent 18th birthday
B11: employee hire data

Employee completion of 5 year
=DATE(YEAR(B11)+5,MONTH(B11),DAY(B11)-1)

number of years between employee 5th year and dependent 18th birthday
=IF(E11<DATE(YEAR(B11)+5,MONTH(B11),DAY(B11)-1),0,DATEDIF(DATE(YEAR(B11) +5, MONTH(B11), DAY(B11) -1),E11,"Y"))