DATEDIF for differences in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate full months between two dates

Given the start date in cell A1 as 2023-01-15 and the end date in cell B1 as 2023-04-10, which formula correctly calculates the number of full months between these dates using DATEDIF?

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

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

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

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

💡 Hint

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

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

📊 Formula Result

intermediate

Calculate days excluding years and months

If cell A1 contains 2022-12-01 and cell B1 contains 2023-02-15, which formula returns the number of days excluding full years and months between these dates?

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

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

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

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

💡 Hint

The "MD" unit returns days ignoring months and years.

The "MD" unit in DATEDIF returns the difference in days ignoring months and years. So it gives the leftover days after counting full months and years.

❓ Function Choice

advanced

Find the formula for total years and leftover months

You want to calculate the total full years and leftover months between two dates in cells A1 and B1. Which pair of formulas correctly gives the years and leftover months respectively?

A Years: =DATEDIF(A1, B1, "Y") and Months: =DATEDIF(A1, B1, "MD")

B Years: =DATEDIF(A1, B1, "M") and Months: =DATEDIF(A1, B1, "MD")

C Years: =DATEDIF(A1, B1, "YD") and Months: =DATEDIF(A1, B1, "MD")

D Years: =DATEDIF(A1, B1, "Y") and Months: =DATEDIF(A1, B1, "YM")

💡 Hint

Use "Y" for full years and "YM" for leftover months ignoring years.

The DATEDIF function with "Y" returns full years. The "YM" unit returns leftover months after counting full years. This pair correctly splits the difference into years and months.

🎯 Scenario

advanced

Identify the error in this DATEDIF formula

A user writes the formula =DATEDIF(B1, A1, "M") where A1 is the start date and B1 is the end date. What will happen when this formula is used?

A It returns a negative number of months.

B It returns a #NUM! error because the start date is after the end date.

C It returns the number of full months between the two dates correctly.

D It returns zero regardless of the dates.

💡 Hint

Check the order of dates in the DATEDIF function.

The DATEDIF function requires the start date as the first argument and the end date as the second. If reversed, it returns a #NUM! error.

❓ data_analysis

expert

Calculate total days excluding full years using DATEDIF

Given start date in A1 as 2019-05-20 and end date in B1 as 2023-08-15, which formula returns the total number of days excluding full years between these dates?

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

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

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

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

💡 Hint

The "YD" unit returns days ignoring years.

The DATEDIF unit "YD" returns the number of days between the dates ignoring full years. This excludes full years but counts leftover days.