EDATE and EOMONTH in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate Date 3 Months Ahead Using EDATE

If cell A1 contains the date 2024-01-15, what will be the result of the formula =EDATE(A1, 3)?

A 2024-04-15

B 2024-03-15

C 2024-04-30

D 2024-05-15

💡 Hint

EDATE adds the number of months to the date in A1, keeping the day number if possible.

The formula =EDATE(A1, 3) adds 3 months to January 15, 2024, resulting in April 15, 2024.

📊 Formula Result

intermediate

Find Last Day of Month 2 Months Before Using EOMONTH

If cell B2 contains the date 2024-06-10, what is the result of =EOMONTH(B2, -2)?

A 2024-04-10

B 2024-05-31

C 2024-04-30

D 2024-06-30

💡 Hint

EOMONTH returns the last day of the month, offset by the number of months.

=EOMONTH(B2, -2) finds the last day of the month two months before June 2024, which is April 30, 2024.

❓ Function Choice

advanced

Choose the Correct Formula to Find the Last Day of Next Month

Which formula correctly returns the last day of the month after the date in cell C3?

A =EOMONTH(C3, 1)

B =EDATE(C3, 1)

C =EOMONTH(C3, 0)

D =EDATE(C3, 0)

💡 Hint

EOMONTH returns the last day of the month offset by months; EDATE returns the same day number offset by months.

=EOMONTH(C3, 1) returns the last day of the month after the month of the date in C3.

🎯 Scenario

advanced

Calculate Subscription Expiry Date 6 Months Later on Last Day of Month

You have a subscription start date in cell D4. You want to find the expiry date exactly 6 months later, but always on the last day of that month. Which formula should you use?

A =EDATE(D4, 6)

B =EDATE(D4, 5)

C =EOMONTH(D4, 5)

D =EOMONTH(D4, 6)

💡 Hint

EOMONTH returns the last day of the month offset by months.

To get the expiry date 6 months later on the last day of that month, use =EOMONTH(D4, 6).

❓ data_analysis

expert

Analyze Formula Output for End of Month with Negative Offset

Given the date 2024-03-31 in cell E5, what is the output of =EOMONTH(E5, -1)?

A 2024-03-01

B 2024-02-29

C 2024-02-28

D 2024-01-31

💡 Hint

Remember 2024 is a leap year with 29 days in February.

=EOMONTH(E5, -1) returns the last day of the month one month before March 2024, which is February 29, 2024 (leap year).

Practice

1. What does the EDATE function do in Google Sheets?

easy

A. Returns a date a specified number of months before or after a start date

B. Returns the last day of the current month

C. Calculates the number of days between two dates

D. Returns the day of the week for a given date

Solution

1. Step 1: Understand the purpose of EDATE

   The EDATE function is designed to add or subtract whole months from a given start date.

2. Step 2: Compare with other date functions

   Unlike functions that return days or weekdays, EDATE specifically works with months to find a new date.

3. Final Answer:

   Returns a date a specified number of months before or after a start date -> Option A

4. Quick Check:

   EDATE = date ± months [OK]

Hint: EDATE shifts dates by months, not days [OK]

Common Mistakes:

• Confusing EDATE with DAY or WEEKDAY functions
• Thinking EDATE returns end of month
• Using EDATE to add days instead of months

2. Which of the following is the correct syntax for the EOMONTH function?

easy

A. EOMONTH(date, days)

B. EOMONTH(months, start_date)

C. EOMONTH(start_date, months)

D. EOMONTH(start_date)

Solution

1. Step 1: Recall EOMONTH syntax

   EOMONTH requires two arguments: the start date and the number of months to move forward or backward.

2. Step 2: Check argument order and count

   The first argument must be the start date, the second is the months offset. The other options have wrong order or missing arguments.

3. Final Answer:

   EOMONTH(start_date, months) -> Option C

4. Quick Check:

   EOMONTH(date, months) correct syntax [OK]

Hint: Start date first, months second in EOMONTH [OK]

Common Mistakes:

• Swapping the order of arguments
• Omitting the months argument
• Using days instead of months

3. Given cell A1 contains the date 2024-01-15, what is the result of =EDATE(A1, 3)?

medium

A. 2024-04-30

B. 2024-04-15

C. 2024-03-31

D. 2024-01-18

Solution

1. Step 1: Understand EDATE with positive months

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

2. Step 2: Calculate 3 months after January 15, 2024

   Adding 3 months to January 15, 2024 results in April 15, 2024.

3. Final Answer:

   2024-04-15 -> Option B

4. Quick Check:

   EDATE(2024-01-15, 3) = 2024-04-15 [OK]

Hint: EDATE keeps day, shifts month by given number [OK]

Common Mistakes:

• Confusing EDATE with EOMONTH output
• Adding days instead of months
• Assuming end of month date

4. You wrote =EOMONTH(A1, 1) but it returns an error. Cell A1 contains the text "March 10, 2024" (not a date). What is the fix?

medium

A. Convert A1 to a valid date format before using EOMONTH

B. Change formula to =EOMONTH("March 10, 2024", 1)

C. Replace EOMONTH with EDATE

D. Use =EOMONTH(A1, "1") with months as text

Solution

1. Step 1: Identify cause of error

   EOMONTH requires a valid date value, but A1 contains text, not a date.

2. Step 2: Fix by converting text to date

   Convert or re-enter A1 as a proper date so EOMONTH can process it correctly.

3. Final Answer:

   Convert A1 to a valid date format before using EOMONTH -> Option A

4. Quick Check:

   EOMONTH needs date input, not text [OK]

Hint: Ensure date cells are real dates, not text [OK]

Common Mistakes:

• Passing text strings instead of dates
• Using quotes around month number
• Switching to wrong function without fixing input

5. You want to find the last day of the month 2 months before the date in cell B2 (which is 2024-05-20). Which formula gives the correct result?

hard

A. =EDATE(B2, 2)

B. =EDATE(B2, -2)

C. =EOMONTH(B2, 2)

D. =EOMONTH(B2, -2)

Solution

1. Step 1: Understand the requirement

   You want the last day of the month, 2 months before May 20, 2024.

2. Step 2: Choose correct function and argument

   EOMONTH returns the last day of a month offset by months. Negative 2 moves back 2 months from May 2024 to March 2024.

3. Step 3: Verify other options

   EDATE returns the same day number, not the last day. Positive 2 moves forward, not backward.

4. Final Answer:

   =EOMONTH(B2, -2) -> Option D

5. Quick Check:

   EOMONTH with negative months finds past month end [OK]

Hint: Use EOMONTH with negative months for past month ends [OK]

Common Mistakes:

• Using EDATE instead of EOMONTH for month-end
• Using positive months to go backward
• Confusing last day with same day number