Help with "Time of Service" formula

[dallen8028]

The formula below calculates "Time of Service" of many employees with different start dates. The years and months calculate correctly but the number of days are all the same when they should be different.

=DATEDIF([@[Seniority Date]],$F$2,"y")&" years," &DATEDIF([@[Seniority Date]],$F$2,"ym")&" months,"&$F$2-DATE(YEAR($F$2),MONTH($F$2),1)&" days"

Using MS Office Pro 2016. Please advise

Thanks in advance.

 

[jakeman]

Try this:

=DATEDIF([@[Seniority Date]],$F$2,"y")&" years," & DATEDIF([@[Seniority Date]],$F$2,"ym")&" months," & DATEDIF([@[Seniority Date]],$F$2,"md")&" days"

If this helped you, please hit the "Like this post" button above.

 

[dallen8028]

Thank you jakeman. Worked great!

 

[jakeman]

You're welcome. Glad it helped. Thanks for leaving feedback!

 

[dallen8028]

Try this:

=DATEDIF([@[Seniority Date]],$F$2,"y")&" years," & DATEDIF([@[Seniority Date]],$F$2,"ym")&" months," & DATEDIF([@[Seniority Date]],$F$2,"md")&" days"

If this helped you, please hit the "Like this post" button above.

Mr. Jakeman, With the above formula. I have an employee that has left. I wish to add a section to the formula to stop time in service on the last day of employment. Please advise. Thanks in advance

 

[jakeman]

What date value is in $F$2? Is that today's date? If you are able to pull employee termination dates out of our HR system, I would use it in your data set. I might try to do something like this, imagining that Termination Date is a real field that you could pull out of your system. If an employee is active then the Termination Date field would be blank and you would apply the previous formula to evaluate the difference between Today() and the Seniority Date, otherwise take the difference between Termination Date and Seniority Date.

=IF([@[Termination Date]]="",DATEDIF([@[Seniority Date]],TODAY(),"y")&" years," & DATEDIF([@[Seniority Date]],TODAY(),"ym")&" months," & DATEDIF([@[Seniority Date]],TODAY(),"md")&" days",DATEDIF([@[Seniority Date]],[@[Termination Date]],"y")&" years," & DATEDIF([@[Seniority Date]],[@[Termination Date]],"ym")&" months," & DATEDIF([@[Seniority Date]],[@[Termination Date]],"md")&" days")

 

[dallen8028]

This works great for what I asked. Thanks. What has to be added in order to keep the "calculated" cell blank when there is no date available? I do not have access to everyone's hire date yet. This will help keep the column clean until dates are inserted. I've tried adding the AND after the first IF and including the other date but it gives me an error of too many arguments. Please advise...?

 

[jakeman]

It would only be necessary to handle blanks when the Seniority Date is not available. So in that case, you could do something like:

=IF([@[Seniority Date]]="","",IF([@[Termination Date]]="",DATEDIF([@[Seniority Date]],TODAY(),"y")&" years," & DATEDIF([@[Seniority Date]],TODAY(),"ym")&" months," & DATEDIF([@[Seniority Date]],TODAY(),"md")&" days",DATEDIF([@[Seniority Date]],[@[Termination Date]],"y")&" years," & DATEDIF([@[Seniority Date]],[@[Termination Date]],"ym")&" months," & DATEDIF([@[Seniority Date]],[@[Termination Date]],"md")&" days"))

 

[dallen8028]

That was perfect, Thanks

 

[jakeman]

You're very welcome. Glad it helped.