Employee Tenure in Years and Months with DATEDIF
Your task: In C2:D2, work out each employee's complete years and extra months of service, then fill them down.
Full instructions, scenario and goal
Scenario
HR is preparing service awards. For each employee they need the complete years of service as of the report date in G1, and the number of extra months beyond those years, for example 7 years and 3 months. Two employees are close to an anniversary, so the day of the month matters.
Your task
- Look at the hire dates in B2:B7 and the report date in G1.
- In C2, calculate the complete years of service from the hire date to the report date.
- In D2, calculate the extra months beyond those complete years.
- Copy C2:D2 and paste them over C3:D7. Select Check Answer.
You are done when
- C2:D7 contain formulas that use the hire dates and the report date.
- Years are complete years: an anniversary that has not happened yet is not counted.
- Extra months are always between 0 and 11.
What you will practise
Measure the time between two dates in complete years and months using DATEDIF.
The idea in 30 seconds
DATEDIF returns the distance between two dates in the unit you choose: "Y" for complete years, "M" for complete months, "D" for days, and "YM" for the months left over after the complete years. It takes the earlier date first, then the later date, then the unit in quotation marks. It counts only whole units, so someone who started on the 30th of a month has not completed a month until the 30th comes round again.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell C2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
DATEDIF returns the distance between two dates in the unit you choose: "Y" for complete years, "M" for complete months, "D" for days, and "YM" for the months left over after the complete years. It takes the earlier date first, then the later date, then the unit in quotation marks. It counts only whole units, so someone who started on the 30th of a month has not completed a month until the 30th comes round again.
Dividing a number of days by 365 looks similar but is only approximate: leap years add a day, so the result can be a year too high on the day before an anniversary. DATEDIF is an older function that Excel keeps for compatibility, so it does not appear in the function list when you type, but it works in every version.
Skills you will practise
- DATEDIF
- Dates
- Complete years
- Complete months
Complete first: Due Dates and Days Overdue
Common mistakes to avoid
- Subtracting the years
- The difference between the two years ignores whether the anniversary has happened yet. Someone hired in November has not completed a year by the following June.
- Dividing days by 365
- Leap days make a year 366 days long, so a day count divided by 365 can round to a year that has not finished.
- Swapping the dates
- DATEDIF needs the earlier date first. With the dates the wrong way round it returns an error.