DATEDIF for date differences in Excel - 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 table that shows each employee's name, start date, and the exact time they have worked in years, months, and days.

Sample Data

Employee Name	Start Date	Today's Date
Alice	2018-03-15	2024-06-01
Bob	2020-07-22	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: Enter the sample data into your spreadsheet with columns: Employee Name, Start Date, Today's Date.

Expected Result

Data table with 5 employees and their start and today's dates.

2

Step 2: In a new column named 'Years Worked', calculate the full years between Start Date and Today's Date using DATEDIF.

=DATEDIF(B2,C2,"Y")

Expected Result

For Alice, the formula returns 6 years.

3

Step 3: In a new column named 'Months Worked', calculate the remaining months after full years using DATEDIF.

=DATEDIF(B2,C2,"YM")

Expected Result

For Alice, the formula returns 2 months.

4

Step 4: In a new column named 'Days Worked', calculate the remaining days after full months using DATEDIF.

=DATEDIF(B2,C2,"MD")

Expected Result

For Alice, the formula returns 17 days.

5

Step 5: Combine the years, months, and days into one readable text string in a new column named 'Total Time Worked'.

=DATEDIF(B2,C2,"Y") & " years, " & DATEDIF(B2,C2,"YM") & " months, " & DATEDIF(B2,C2,"MD") & " days"

Expected Result

For Alice, the cell shows '6 years, 2 months, 17 days'.

6

Step 6: Copy the formulas down for all employees to see their time worked.

Drag down the formulas from row 2 to row 6.

Expected Result

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

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-22 | 2024-06-01   | 3            | 10           | 10          | 3 years, 10 months, 10 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 over 6 years, showing long-term experience.

✓Bob has nearly 4 years of service, close to 3 years and 10 months.

✓Charlie has over 4 and a half years of work time.

✓Diana is a newer employee with just over 2 years of service.

✓Edward has the longest tenure with almost 7 years at the company.

Bonus Challenge

Create a formula that calculates the total number of days each employee has worked.

Show Hint

Use the DATEDIF function with the "D" unit to get total days between two dates.