DATEDIF for date differences in Excel - Step-by-Step Guide

Introduction

DATEDIF helps you find the difference between two dates in days, months, or years. It is useful when you want to know how much time has passed between two events, like calculating age or project duration.

When you want to calculate a person's age from their birthdate and today's date.

When you need to find how many months have passed between a project start and end date.

When you want to know the exact number of days between two appointments.

When you want to calculate the number of complete years between two dates for service duration.

When you want to track how many months remain until a deadline.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for typing

Step 2: Type

- the formula bar

The formula appears in the cell as you type

💡 Start typing =DATEDIF( to see the function suggestions

Step 3: Enter

- the formula: =DATEDIF(start_date, end_date, unit)

The cell shows the difference between the two dates based on the unit you choose

💡 Replace start_date and end_date with cell references or dates in quotes, and unit with "d", "m", or "y"

Step 4: Press Enter

- after typing the formula

Excel calculates and displays the date difference

Before vs After

Before

Cell A1 has 2023-01-01, Cell B1 has 2024-06-01, Cell C1 is empty

After

Cell C1 shows 517 when formula =DATEDIF(A1, B1, "d") is entered

Settings Reference

start_date

📍 inside the DATEDIF formula

The earlier date to start counting from

Default: none

end_date

📍 inside the DATEDIF formula

The later date to count up to

Default: none

unit

📍 inside the DATEDIF formula

Defines how the difference is measured

Default: none

Common Mistakes

Using end_date before start_date

DATEDIF returns an error if the start date is after the end date

Always put the earlier date first as start_date and the later date second as end_date

Typing unit without quotes

The unit must be a text string in quotes, otherwise Excel shows an error

Always put the unit inside double quotes, like "d", "m", or "y"

Using DATEDIF with invalid dates or text

If dates are not valid Excel dates, the formula will not calculate correctly

Make sure start_date and end_date are valid dates or cell references containing dates

Summary

DATEDIF calculates the difference between two dates in days, months, or years.

You must enter the earlier date first and the later date second.

The unit argument must be a text string in quotes: "d", "m", or "y".

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