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.

Practice

1. What does the Excel function =DATEDIF(A1, B1, "M") calculate?

easy

A. The number of years between the dates in A1 and B1

B. The number of days between the dates in A1 and B1

C. The sum of the dates in A1 and B1

D. The number of complete months between the dates in A1 and B1

Solution

1. Step 1: Understand the DATEDIF function parameters

   The function takes a start date, an end date, and a unit to measure difference.

2. Step 2: Interpret the unit "M"

   "M" means the difference in complete months between the two dates.

3. Final Answer:

   The number of complete months between the dates in A1 and B1 -> Option D

4. Quick Check:

   DATEDIF with "M" returns months [OK]

Hint: Use "M" to get months difference, not days or years [OK]

Common Mistakes:

• Confusing "M" with days or years
• Putting the later date first causing errors
• Thinking it sums dates instead of difference

2. Which of the following is the correct syntax to find the number of days between dates in cells A2 and B2 using DATEDIF?

easy

A. =DATEDIF(B2, A2, "D")

B. =DATEDIF(A2, B2, "D")

C. =DATEDIF(A2, B2, "Days")

D. =DATEDIF(A2, B2, D)

Solution

1. Step 1: Check the order of dates

   The earlier date must be first, so A2 then B2 is correct.

2. Step 2: Verify the unit parameter

   "D" in quotes is the correct unit for days; "Days" or D without quotes is invalid.

3. Final Answer:

   =DATEDIF(A2, B2, "D") -> Option B

4. Quick Check:

   Correct syntax uses "D" and earlier date first [OK]

Hint: Always put earlier date first and use "D" in quotes [OK]

Common Mistakes:

• Swapping start and end dates
• Using unit without quotes
• Using incorrect unit names like "Days"

3. Given A1 = 2023-01-15 and B1 = 2024-03-10, what is the result of =DATEDIF(A1, B1, "Y")?

medium

A. 14

B. 2

C. 1

D. 0

Solution

1. Step 1: Understand the dates and unit "Y"

   "Y" returns the number of complete years between the two dates.

2. Step 2: Calculate complete years from 2023-01-15 to 2024-03-10

   Only one full year (2023-01-15 to 2024-01-15) has passed.

3. Final Answer:

   1 -> Option C

4. Quick Check:

   DATEDIF with "Y" counts full years only [OK]

Hint: DATEDIF "Y" counts full years, ignore partial months/days [OK]

Common Mistakes:

• Counting partial years as full
• Confusing "Y" with months or days
• Misreading date formats

4. You entered =DATEDIF(A1, B1, "M") but got a #NUM! error. What is the most likely cause?

medium

A. The end date in B1 is earlier than the start date in A1

B. The unit "M" is not valid for DATEDIF

C. You forgot to put quotes around "M"

D. The dates are not formatted as text

Solution

1. Step 1: Understand #NUM! error in DATEDIF

   This error happens if the start date is after the end date.

2. Step 2: Check the order of dates in A1 and B1

   If B1 is earlier than A1, DATEDIF returns #NUM! error.

3. Final Answer:

   The end date in B1 is earlier than the start date in A1 -> Option A

4. Quick Check:

   #NUM! means start date > end date [OK]

Hint: Ensure start date is before end date to avoid #NUM! error [OK]

Common Mistakes:

• Swapping dates causing negative difference
• Using invalid units without quotes
• Assuming date format causes error

5. You want to calculate the total number of months and days between two dates in A1 and B1, showing result as "X months and Y days". Which formula correctly does this?

hard

A. =DATEDIF(A1,B1,"M") & " months and " & DATEDIF(A1,B1,"MD") & " days"

B. =DATEDIF(A1,B1,"M") + DATEDIF(A1,B1,"D") & " months and days"

C. =DATEDIF(A1,B1,"YM") & " months and " & DATEDIF(A1,B1,"D") & " days"

D. =DATEDIF(A1,B1,"Y") & " months and " & DATEDIF(A1,B1,"MD") & " days"

Solution

1. Step 1: Understand the units needed

   "M" gives total complete months, "MD" gives leftover days ignoring months and years.

2. Step 2: Combine these with text to form the phrase

   Concatenate months, text, and days correctly using & and quotes.

3. Final Answer:

   =DATEDIF(A1,B1,"M") & " months and " & DATEDIF(A1,B1,"MD") & " days" -> Option A

4. Quick Check:

   Use "M" and "MD" units combined with & for text [OK]

Hint: Use "M" and "MD" units with & to combine months and days [OK]

Common Mistakes:

• Adding months and days instead of concatenating
• Using wrong units like "YM" or "Y" for months
• Forgetting quotes around text parts