EDATE and EOMONTH in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate a Date 3 Months Ahead Using EDATE

You have a date in cell A1: 2024-01-15. Which formula correctly calculates the date exactly 3 months later?

A =EOMONTH(A1, 3)

B =EDATE(A1, 3)

C =EDATE(A1, -3)

D =EOMONTH(A1, -3)

💡 Hint

EDATE adds months to a date, keeping the day number if possible.

The EDATE function adds a specified number of months to a date. Here, adding 3 months to January 15, 2024, results in April 15, 2024.

EOMONTH returns the last day of the month, so options B and D give different results.

📊 Formula Result

intermediate

Find the Last Day of the Previous Month Using EOMONTH

Given a date in cell B2: 2024-05-10, which formula returns the last day of the previous month?

A =EOMONTH(B2, -1)

B =EOMONTH(B2, 0)

C =EDATE(B2, -1)

D =EDATE(B2, 0)

💡 Hint

EOMONTH with -1 returns the end of the previous month.

EOMONTH(B2, -1) returns the last day of the month before the date in B2. For May 10, 2024, it returns April 30, 2024.

Other options either return the current month's end or shift the date by months without giving the month's end.

❓ Function Choice

advanced

Choose the Correct Function to Calculate the Last Day of a Month 6 Months Ahead

You want to find the last day of the month that is 6 months after the date in cell C3. Which formula should you use?

A =EDATE(C3, 6)

B =EOMONTH(C3, 0)+6

C =EDATE(C3, 6)+DAY(EOMONTH(C3, 6))

D =EOMONTH(C3, 6)

💡 Hint

EOMONTH returns the last day of the month after adding months.

EOMONTH(C3, 6) returns the last day of the month 6 months after the date in C3.

EDATE returns the same day number 6 months later, not the month's end.

Options B and D are incorrect because they add days incorrectly or combine functions improperly.

🎯 Scenario

advanced

Calculate Subscription Expiry Date Exactly 1 Year Later

A subscription starts on the date in cell D4. You want to calculate the expiry date exactly 1 year later, keeping the same day number if possible. Which formula is best?

A =EOMONTH(D4, 12)

B =D4 + 365

C =EDATE(D4, 12)

D =EOMONTH(D4, 11)

💡 Hint

EDATE adds months and keeps the day number if possible.

EDATE(D4, 12) adds 12 months (1 year) to the date in D4, keeping the same day number if possible.

EOMONTH(D4, 12) returns the last day of the month 12 months later, which may not be the same day number.

Adding 365 days ignores leap years and may be inaccurate.

❓ data_analysis

expert

Analyze the Result of Combining EDATE and EOMONTH

You have a date in cell E5: 2024-01-31. What is the result of the formula =EOMONTH(EDATE(E5, 1), 0)?

A 2024-02-29 (last day of February 2024)

B 2024-02-28 (last day of February 2024)

C 2024-03-31 (last day of March 2024)

D 2024-01-31 (original date)

💡 Hint

EDATE moves the date by months, adjusting day if needed; EOMONTH returns month's end.

EDATE(E5, 1) adds 1 month to January 31, 2024. Since February 31 does not exist, it adjusts to February 29, 2024 (leap year).

EOMONTH(..., 0) returns the last day of the month of the date given, which is February 29, 2024.