Help on formula

[TDD]

Need help on what formula to use to calculate the tenure of an employee from hire date in one cell and have that cell tenure count change once I put the resignation date of the employee in another cell

 

[JRenn]

Hi, not 100% sure of what you are after but try this. Start Date in B1, Resignation Date in B2. Enter the below formula in C1.
=IF(B2="",DATEDIF(B1,TODAY(),"y")&" years,"&DATEDIF(B1,TODAY(),"ym")&" months,"&DATEDIF(B1,TODAY(),"md")&" days",DATEDIF(B1,B2,"y")&" years,"&DATEDIF(B1,B2,"ym")&" months,"&DATEDIF(B1,B2,"md")&" days")
If no resignation, B2 is blank and tenure to today, otherwise to resignation date.

 

[Dave Patton]

an alternative to consider
=IF(B2="","",ROUND(YEARFRAC(B1,B2,1),1))

Review Excel's help for information on the functions.

 

[TDD]

Hi, not 100% sure of what you are after but try this. Start Date in B1, Resignation Date in B2. Enter the below formula in C1.
=IF(B2="",DATEDIF(B1,TODAY(),"y")&" years,"&DATEDIF(B1,TODAY(),"ym")&" months,"&DATEDIF(B1,TODAY(),"md")&" days",DATEDIF(B1,B2,"y")&" years,"&DATEDIF(B1,B2,"ym")&" months,"&DATEDIF(B1,B2,"md")&" days")
If no resignation, B2 is blank and tenure to today, otherwise to resignation date.

Thanks!

 

[TDD]

an alternative to consider
=IF(B2="","",ROUND(YEARFRAC(B1,B2,1),1))

Review Excel's help for information on the functions.

Thanks!

 

[Brunts]

I wouldn't put implicit trust in the DATEDIF(B1,B2,"md")&" days" element of the formula, as it can generate negative numbers.
Example:-
Start date End date Generated Output
31/10/1985 01/03/2020 34 years, 4 months, -1 days

Microsoft are aware - DATEDIF function - they have a work around, which doesn't work too well either, particularly if the Start date is in a leap year.

This alternative seems to be more accurate than the current solutions offered, if a bit convoluted, but clearly would be better if Microsoft could come up with a 100% accurate formula
=DATEDIF(B1,B2,"y")&" years, " & DATEDIF(B1,B2,"ym")&" months, " & IF(DAY(B2)>=DAY(B1),DAY(B2)-DAY(B1),DAY(EOMONTH(B1, 0))-DAY(B1)+DAY(B2))&" days"