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.

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

On Dec 22, 12:36*pm, Kim wrote:
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.

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.

On Dec 22, 7:36*am, Kim wrote:
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.

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" wrote:

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

On Dec 22, 12:36 pm, Kim wrote:
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.

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

On Dec 22, 1:19*pm, Kim wrote:
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" wrote:
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

On Dec 22, 12:36 pm, Kim wrote:
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.- Hide quoted text -

- Show quoted text -

You can use the DATEDIF function in this way:

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

Hope this helps.

Pete

On Dec 22, 1:19*pm, Kim wrote:
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" wrote:
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

On Dec 22, 12:36 pm, Kim wrote:
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.- Hide quoted text -

- Show quoted text -