Employee Length of Service

[Kim]

Can anyone help please? I want to calculate employee length of service from
start date (A1) where there is a leave date (B1) or (B1) is blank and should
use TODAY as the reference date.

 

[Pete_UK]

You could use the DATEDIF function to give you the difference between
two dates in years, months or days - take a look here on how to use
it:

http://www.cpearson.com/excel/datedif.aspx

A simple approach, to give you the difference in days, would be:

=IF(B1="",TODAY()-A1,B1-A1)

Format the cell as General or Number.

Hope this helps.

Pete

 

[Reitanos]

Try DATEDIF.

You could use the following if you want to see the number of years and
the number of days that are not a complete year:
=DATEDIF(A1,A2,"y")&" years, "&DATEDIF(A1,A2,"yd")&" days"

Replace both A2 references with IF(ISBLANK(A2),TODAY(),A2) for the
blank issue.

 

[Kim]

Thanks for the reply. Is there a way to show the result in years rather than
days or to use DATEDIF with an additional function (IF?) to use either TODAY
or the leave date?

 

[Pete_UK]

If you want to use 365 days per year, then you can do this with the
formula I posted earlier:

=IF(B1="",TODAY()-A1,B1-A1)/365

This will give you fractional years, eg 1.5, but if you only need
whole years you can put INT( ... ) or ROUNDDOWN( ... ,0) around it
all.

Hope this helps.

Pete

 

[Pete_UK]

You can use the DATEDIF function in this way:

=DATEDIF(A1,IF(B1="",TODAY(),B1),"y")

Hope this helps.

Pete