Date Functions
Advanced

Length of service in years and months

"Y" for the whole years, "YM" for the months left over.

Task:

The long-service report shows each person's service as, for example, 5 y 3 m. In C2:C6 build that text from the start date and the report date in E1.

Interactive Spreadsheet

Read about DATEDIF

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of DATEDIF

0/3

The same formula in the other shapes it takes at work.

  1. DATEDIF Function

    Calculate the difference between two dates using DATEDIF.

    Core
    Advanced
  2. DATEDIF in whole months

    The "M" unit counts completed months.

    Intermediate
  3. Length of service in years and months

    "Y" for the whole years, "YM" for the months left over.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 5 columns5 cells you fill in
ABCDE
1EmployeeStart dateServiceReport date3/31/2025
2Quinn12/15/2019
3Rhea3/31/2015
4Sol7/1/2023
5Tam4/1/2024
6Uma9/30/2010
What this exercise teachesMay contain the answer

"YM" ignores the years and counts only the months beyond the last anniversary, which is exactly the remainder you want. Rhea started ten years ago to the day, so she shows 10 y 0 m.