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

By Ali, Founder, Excel Lessons

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`, type:

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

B7 holds the as-of date, locked so it does not move.

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, type: =DATEDIF(B2,$B$7,"y"). B7 holds the as-of date, locked so it does not move.

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

In E2, use DATEDIF on C2 and B7 for years of service.

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

The unit ym counts months left after whole years. In F2, write Ada's service as 4 y 9 m. Shape: =DATEDIF(start, cell, "y")&" y "&DATEDIF(start, cell, "ym")&" m".

Hint. Join two DATEDIF results into one line of text.
5

The unit yd counts days since the last whole year. In E6, use DATEDIF on C4 and B7 for days since Cai's anniversary.

Hint. Unit "yd".
6

The unit m counts all the months, not the leftover ones. Read =DATEDIF(C2,$B$7,"m") and say what it shows for Ada.

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