Formuladrill

Level 4: Text and dates Exercise 72 of 118

Completed years of service

An Excel formula exercise from Formuladrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.

The task

Work out each employee's completed years of service on the date in F1. A year only counts once it is complete: someone hired on 2019-07-01 has 6 full years on 2026-06-30. Fill C2 and C3.

What this exercise teaches

DATEDIF counts complete units: "Y" for years, "M" for months, "D" for days. It is the reliable way to get an age or a length of service, because dividing the days by 365 goes wrong around leap years and anniversaries.

A hint if you get stuck

DATEDIF(start, end, "Y") returns the number of complete years between two dates. Excel does not suggest this function as you type, but it works.

Functions used: DATEDIF

Try it now, free, no account needed

Your first 15 exercises on Formuladrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every exercise, timed mode and the certificate.

Open this exercise

More exercises in Text and dates