Suggestions on DATEDIF formula

[spectraflame]

I need to calculate age based on DOB and display it in Years, Months, Day. I am using this formula: =IF(DATEDIF(D2,NOW(),"Y")>0,DATEDIF(D2,NOW(),"Y")& " YEARS","")&IF(AND(DATEDIF(D2,NOW(),"Y")>0,DATEDIF(D2,NOW(),"YM")>0),", ","")&IF(DATEDIF(D2,NOW(),"YM")>0,DATEDIF(D2,NOW(),"YM")&" MONTHS","")&IF(AND(DATEDIF(D2,NOW(),"YM")>0,DATEDIF(D2,NOW(),"MD")>0),", ","")&IF(DATEDIF(D2,NOW(),"MD")>0,DATEDIF(D2,NOW(),"MD")&" DAYS","")

I am also using the same formula to calculate the years of service an employee has. Again, this works as desired.

Our retirement eligibility is based on age + years of service. HR would like to combine the data to show retirement status in years, months, and days.

I am just not able to figure out a simple method. Any suggestions?

 

[Tetra201]

Try putting the following in cell D2: = Date_Of_Birth + Start_Of_Service - NOW()

 

[spectraflame]

Try putting the following in cell D2: = Date_Of_Birth + Start_Of_Service - NOW()

DOB = 1/1/974, START_DATE = 06/17/2022, result is 19327.35 or 52 Years, 11 Months, 29 days when formatted at YY,MM,DD. Thinking it should be 49 years of age based on today, 21 years of service based on today so the total years would be 70.

 

[Tetra201]

For having 21 years of service, the start date should be no later than 7/19/2002.

Let's assume you have:
- a DOB of 1/1/1974 in cell B2;
- a start date of 6/17/2002 in cell C2;
- =B2+C2-NOW() in cell D2 (evaluates to 11/29/1952);
- your DATEDIF-based formula in cell E2 (evaluates to "70 YEARS, 7 MONTHS, 20 DAYS").

 

[awoohaw]

maybe this can help for a portion of your question. I'm not sure about the time of service part, but will continue to look into it:

Book1
	A	B	C	D	E
3
4	Current Date	2023-07-19
5
6	DOB		Age Years	Age Months	Age Days
7	1974-01-25		49	5	24
8	1974-01-01		49	6	18
9
Sheet1

Cell Formulas
Range	Formula
C7:C8	C7	=DATEDIF(A7,$B$4,"Y")
D7:D8	D7	=DATEDIF(DATE(YEAR($B$4),MONTH(A7),DAY(A7)),$B$4,"M")
E7:E8	E7	=$B$4-DATE(YEAR($B$4),MONTH(A7)+D7,DAY(A7))

 

[spectraflame]

For having 21 years of service, the start date should be no later than 7/19/2002.

Let's assume you have:
- a DOB of 1/1/1974 in cell B2;
- a start date of 6/17/2002 in cell C2;
- =B2+C2-NOW() in cell D2 (evaluates to 11/29/1952);
- your DATEDIF-based formula in cell E2 (evaluates to "70 YEARS, 7 MONTHS, 20 DAYS").

Sorry. Start would have been 2002

 

[spectraflame]

I appreciate the detailed explanation. Again I was trying to make this more difficult than it had to be. I can just hide that cell that is doing the B2+C2-NOW() and that will return what HR is looking for. Thanks again.

 

[Tetra201]

You are welcome.