DATEDIF for date differences in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate full years between two dates

You have a start date in cell A1 (01/01/2015) and an end date in cell B1 (01/01/2023). Which formula correctly calculates the number of full years between these two dates using DATEDIF?

A =DATEDIF(A1, B1, "D")

B =DATEDIF(A1, B1, "M")

C =DATEDIF(A1, B1, "Y")

D =DATEDIF(B1, A1, "Y")

💡 Hint

Remember, the third argument in DATEDIF specifies the unit: "Y" for years, "M" for months, "D" for days.

The DATEDIF function calculates the difference between two dates. Using "Y" returns the number of full years between the start and end dates. Option C correctly uses the start date first and the unit "Y".

📊 Formula Result

intermediate

Calculate months excluding years

Given a start date in A1 (15/01/2020) and an end date in B1 (10/06/2023), which formula returns the number of months after counting full years (i.e., months excluding full years) between the two dates?

A =DATEDIF(A1, B1, "YM")

B =DATEDIF(A1, B1, "M")

C =DATEDIF(B1, A1, "YM")

D =DATEDIF(A1, B1, "Y")*12

💡 Hint

Use the unit that counts months ignoring full years.

The unit "YM" in DATEDIF returns the number of months after subtracting full years. Option A correctly uses this to get months excluding years.

❓ Function Choice

advanced

Choose the correct DATEDIF formula for total days difference

You want to find the total number of days between two dates in A1 and B1. Which formula correctly uses DATEDIF to get this total days difference?

A =DATEDIF(A1, B1, "D")

B =DATEDIF(B1, A1, "D")

C =DATEDIF(A1, B1, "MD")

D =DATEDIF(A1, B1, "Y")*365

💡 Hint

The unit "D" returns total days difference.

The unit "D" returns the total number of days between two dates. Option A returns days ignoring months and years, which is not total days. Option A reverses dates causing errors or negative results. Option A approximates days but is not exact.

🎯 Scenario

advanced

Calculate age in years, months, and days

You have a birthdate in cell A1 (10/03/1990) and today's date in B1 (25/06/2024). Which set of formulas correctly calculates the age as full years, remaining months, and remaining days?

A Years: =DATEDIF(B1, A1, "Y")<br>Months: =DATEDIF(B1, A1, "YM")<br>Days: =DATEDIF(B1, A1, "MD")

B Years: =DATEDIF(A1, B1, "Y")<br>Months: =DATEDIF(A1, B1, "YM")<br>Days: =DATEDIF(A1, B1, "MD")

C Years: =DATEDIF(A1, B1, "Y")<br>Months: =DATEDIF(A1, B1, "M")<br>Days: =DATEDIF(A1, B1, "D")

D Years: =DATEDIF(A1, B1, "Y")<br>Months: =DATEDIF(A1, B1, "MD")<br>Days: =DATEDIF(A1, B1, "YM")

💡 Hint

Use "Y" for years, "YM" for months ignoring years, and "MD" for days ignoring months and years.

Option B correctly uses DATEDIF units to calculate full years, leftover months, and leftover days. Option B reverses dates causing errors. Option B uses "M" and "D" which count total months and days, not leftover parts. Option B mixes units incorrectly.

📊 Formula Result

expert

What error occurs with reversed dates in DATEDIF?

If cell A1 contains 01/01/2025 and B1 contains 01/01/2020, what happens when you use the formula =DATEDIF(A1, B1, "Y")?

A #VALUE! error

B Returns 5

C Returns -5

D #NUM! error

💡 Hint

DATEDIF requires the start date to be earlier than the end date.

When the start date is after the end date, DATEDIF returns a #NUM! error because it cannot calculate a negative difference.