DATEDIF for differences in Google Sheets - 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.

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

When you need to find how many months have passed since a project started.

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

When you want to calculate the difference in years between two dates for a report.

When you want to track how long an employee has worked in months or years.

Steps

Step 1: Click

- the cell where you want the result

The cell is selected and ready for typing

Step 2: Type

- the formula bar

The formula is entered in the cell

💡 Start typing =DATEDIF(

Step 3: Enter

- the start date cell reference or date in quotes

The first date is added to the formula

Step 4: Type

- the formula bar after a comma

The formula waits for the next input

Step 5: Enter

- the end date cell reference or date in quotes

The second date is added to the formula

Step 6: Type

- the formula bar after a comma

The formula waits for the unit input

Step 7: Enter

- the unit in quotes: "Y" for years, "M" for months, or "D" for days

The formula is complete and ready to calculate

Step 8: Press

- Enter key

The cell shows the difference between the two dates in the chosen unit

Before vs After

Before

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

After

Cell C1 shows 3 when formula =DATEDIF(A1, B1, "Y") is entered

Settings Reference

Start Date

📍 First argument in the DATEDIF formula

The earlier date to compare from

Default: No default, must be provided

End Date

📍 Second argument in the DATEDIF formula

The later date to compare to

Default: No default, must be provided

Unit

📍 Third argument in the DATEDIF formula

Defines the unit of difference to calculate

Default: No default, must be provided

Common Mistakes

Using the wrong unit code without quotes, like Y instead of "Y"

Google Sheets treats unquoted letters as cell references or errors

Always put the unit in double quotes, for example "Y"

Entering the end date before the start date

DATEDIF returns an error or unexpected result if the start date is after the end date

Make sure the start date is earlier than or equal to the end date

Using dates that are not recognized as dates by Google Sheets

The formula cannot calculate differences if dates are text or invalid

Ensure dates are valid date values or use DATE function to create dates

Summary

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

You must provide start date, end date, and unit in quotes for the formula to work.

Make sure the start date is before the end date and the unit is typed correctly.

Practice

1. What does the formula =DATEDIF(A1, B1, "D") calculate in Google Sheets?

easy

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

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

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

D. The sum of the dates in A1 and B1

Solution

1. Step 1: Understand the DATEDIF function parameters

   DATEDIF takes two dates and a unit to calculate the difference.

2. Step 2: Interpret the unit "D"

   "D" means the difference in days between the two dates.

3. Final Answer:

   The number of days between the dates in A1 and B1 -> Option A

4. Quick Check:

   DATEDIF with "D" returns days [OK]

Hint: Use "D" for days difference in DATEDIF [OK]

Common Mistakes:

• Confusing "D" with months or years
• Swapping the order of dates
• Using incorrect unit letters

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

easy

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

B. =DATEDIF(A2, B2, M)

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

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

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 syntax

   The unit must be a text string in quotes, "M" for months is correct.

3. Final Answer:

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

4. Quick Check:

   Correct syntax uses quotes and earlier date first [OK]

Hint: Put earlier date first and use quotes around unit [OK]

Common Mistakes:

• Swapping date order
• Missing quotes around unit
• Using full words instead of unit letters

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

medium

A. 0

B. 1

C. 12

D. 365

Solution

1. Step 1: Identify the dates and unit

   A1 is Jan 1, 2023; B1 is Jan 1, 2024; unit "Y" means full years difference.

2. Step 2: Calculate full years between the dates

   From Jan 1, 2023 to Jan 1, 2024 is exactly 1 full year.

3. Final Answer:

   1 -> Option B

4. Quick Check:

   DATEDIF with "Y" counts full years [OK]

Hint: Use "Y" for full years difference [OK]

Common Mistakes:

• Confusing days or months with years
• Expecting partial years to count
• Using wrong date order

4. You entered =DATEDIF(A1, B1, "d") but get an error. What is the likely problem?

medium

A. The unit "d" should be uppercase "D"

B. The dates are in the wrong order

C. DATEDIF does not support "d" as a unit

D. The formula needs an extra argument

Solution

1. Step 1: Check the unit case sensitivity

   DATEDIF units are case sensitive; "D" must be uppercase for days.

2. Step 2: Confirm other syntax is correct

   Dates order and arguments are fine; only unit case causes error.

3. Final Answer:

   The unit "d" should be uppercase "D" -> Option A

4. Quick Check:

   Units are case sensitive in DATEDIF [OK]

Hint: Use uppercase letters for units in DATEDIF [OK]

Common Mistakes:

• Using lowercase unit letters
• Swapping date order
• Adding extra arguments unnecessarily

5. You want to find the number of complete months and leftover days between two dates in A1 and B1. Which formula combination correctly calculates this?

hard

A. =DATEDIF(A1, B1, "M") + DATEDIF(A1, B1, "D")

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

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

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

Solution

1. Step 1: Understand the units for months and leftover days

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

2. Step 2: Combine the two DATEDIF calls correctly

   Concatenate months and days with text for clear output.

3. Final Answer:

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

4. Quick Check:

   Use "M" and "MD" together for months and leftover days [OK]

Hint: Use "M" plus "MD" to get months and leftover days [OK]

Common Mistakes:

• Adding months and total days directly
• Using "YM" which excludes years incorrectly
• Mixing years with leftover days instead of months