DATEDIF for differences in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a human resources assistant at a company.

📋 Request: Your manager wants to know how long each employee has worked at the company in years, months, and days.

📊 Data: You have a list of employees with their start dates and today's date.

🎯 Deliverable: Create a report that shows each employee's name, start date, and the exact difference from their start date to today in years, months, and days.

Sample Data

Employee Name	Start Date	Today's Date
Alice	2018-03-15	2024-06-01
Bob	2020-07-20	2024-06-01
Charlie	2019-11-05	2024-06-01
Diana	2022-01-10	2024-06-01
Edward	2017-06-30	2024-06-01

1

Step 1: Insert a new column named 'Years Worked' next to 'Today's Date'.

In the first cell under 'Years Worked' (e.g., D2), enter the formula: =DATEDIF(B2,C2,"Y")

Expected Result

For Alice, the result is 6 (years).

2

Step 2: Insert a new column named 'Months Worked' next to 'Years Worked'.

In the first cell under 'Months Worked' (e.g., E2), enter the formula: =DATEDIF(B2,C2,"YM")

Expected Result

For Alice, the result is 2 (months).

3

Step 3: Insert a new column named 'Days Worked' next to 'Months Worked'.

In the first cell under 'Days Worked' (e.g., F2), enter the formula: =DATEDIF(B2,C2,"MD")

Expected Result

For Alice, the result is 17 (days).

4

Step 4: Copy the formulas in 'Years Worked', 'Months Worked', and 'Days Worked' down for all employees.

Drag the fill handle from D2:F2 down to the last employee row.

Expected Result

All employees have their years, months, and days worked calculated correctly.

5

Step 5: Create a new column named 'Total Time Worked' to combine years, months, and days into one text string.

In the first cell under 'Total Time Worked' (e.g., G2), enter the formula: =D2 & " years, " & E2 & " months, " & F2 & " days"

Expected Result

For Alice, the result is '6 years, 2 months, 17 days'.

6

Step 6: Copy the 'Total Time Worked' formula down for all employees.

Drag the fill handle from G2 down to the last employee row.

Expected Result

All employees have a readable total time worked string.

Final Result

Employee Name | Start Date | Today's Date | Years Worked | Months Worked | Days Worked | Total Time Worked
---------------------------------------------------------------------------------------------
Alice         | 2018-03-15 | 2024-06-01   | 6            | 2            | 17          | 6 years, 2 months, 17 days
Bob           | 2020-07-20 | 2024-06-01   | 3            | 10           | 12          | 3 years, 10 months, 12 days
Charlie       | 2019-11-05 | 2024-06-01   | 4            | 6            | 27          | 4 years, 6 months, 27 days
Diana         | 2022-01-10 | 2024-06-01   | 2            | 4            | 22          | 2 years, 4 months, 22 days
Edward        | 2017-06-30 | 2024-06-01   | 6            | 11           | 2           | 6 years, 11 months, 2 days

✓Alice has worked for 6 years, 2 months, and 17 days.

✓Bob has worked for almost 4 years, missing about 2 months.

✓Edward has the longest tenure with nearly 7 years.

✓Diana is the newest employee with just over 2 years.

Bonus Challenge

Create a conditional formatting rule to highlight employees who have worked more than 5 years.

Show Hint

Use a rule that checks if the 'Years Worked' column is greater than 5 and apply a fill color.