Ages and Years of Service

About this lesson

An age or a length of service in Excel is DATEDIF from the earlier date to the as-of date, with the unit y. That counts only the complete years between them.

By the end you can

work out an age or years of service in whole years.
write tenure as years and months with two DATEDIFs.

Practises DATEDIF YEAR TODAY

The idea

Age is complete years, and only DATEDIF counts those. Subtracting the years with YEAR() is wrong for everyone whose birthday has not come yet. Dividing the days by 365.25 is wrong on and around birthdays. DATEDIF with "y" is right every day of the year. That is why a function Excel barely documents is on every HR sheet.

The remainder units are the second half of the job. "ym" gives the months past the whole years. "yd" gives the days past them. So 4 y 9 m is two DATEDIFs joined with text. The mistake is "m" where "ym" was meant. That is total months since the start, not the months since the last anniversary.

The mistake to watch for

Computing an age by subtracting years or dividing days by 365.25. Both are wrong for everyone whose birthday has not come yet this year. DATEDIF with y counts only complete years and is right every day. The second trap is m where ym was meant. That is total months since the start, not the months since the last anniversary.

Where this comes up again

DATEDIF: The Undocumented One Dates and Time
Date Arithmetic: Subtracting Dates Dates and Time
Dates and Time: Putting It Together Dates and Time
Bold, AutoSum and the Date Stamp Keyboard Shortcuts

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In D2, Ada's age in whole years on the as-of date in B7, with DATEDIF and the unit y.

To begin, type it exactly:

=DATEDIF(B2,$B$7,"y")

Row	A	B	C	D	E	F	H	I
1	Name	Born	Started	Age	Years here	Service	Born shown	Started shown
2	Ada	33970	43617				1 Jan 1993	1 Jun 2019
3	Ben	32344	44228				20 Jul 1988	1 Feb 2021
4	Cai	36892	45108				1 Jan 2001	1 Jul 2023
5	Dee	30317	40179				1 Jan 1983	1 Jan 2010
6
7	As of	45366
8

Every step

1

In D2, Ada's age in whole years on the as-of date in B7, with DATEDIF and the unit y. To begin, type it exactly: =DATEDIF(B2,$B$7,"y").

Hint. Unit "y", as-of date locked.
2

In E2, Ada's complete years of service, from her start date.

Hint. Same shape as D2, column C.
3

Read =DATEDIF(C3,$B$7,"m") and say what it returns for Ben, in months.

Hint. Whole months, all of them.
4

In F2, write Ada's service as text in the form 4 y 9 m. That is the whole years, then the months left over after those years. Use the unit ym for the second part.

Hint. Two DATEDIFs, & between everything.
5

In E6, write a formula using DATEDIF that gives the number of days since Cai's last work anniversary. The unit yd counts days past the last whole year.

Hint. Unit "yd".
6

The trap. Somebody wrote Ada's service as years and months, but used the unit m for the second part instead of ym. Read =DATEDIF(C2,$B$7,"m") and say what the months part came out as.

Hint. All the months, or the ones left over?