EDATE and EOMONTH in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a financial analyst at a small business.

📋 Request: Your manager wants a report showing the due dates for monthly invoices and the last day of each invoice month for the next 6 months.

📊 Data: You have a list of invoice start dates in a column.

🎯 Deliverable: Create a table that shows for each invoice start date: the due date exactly 3 months later and the last day of that due month.

Sample Data

Invoice ID	Start Date
INV001	2024-01-15
INV002	2024-02-10
INV003	2024-03-05
INV004	2024-04-20
INV005	2024-05-25
INV006	2024-06-30

1

Step 1: Add a new column named 'Due Date (3 months later)' next to the 'Start Date' column.

In cell C2, enter the formula: =EDATE(B2, 3)

Expected Result

For INV001, the due date will be 2024-04-15.

2

Step 2: Copy the formula in C2 down to all rows to calculate due dates for all invoices.

Drag the fill handle from C2 down to C7.

Expected Result

Due dates for all invoices are calculated 3 months after their start dates.

3

Step 3: Add another column named 'End of Due Month' next to the 'Due Date (3 months later)' column.

In cell D2, enter the formula: =EOMONTH(C2, 0)

Expected Result

For INV001, the end of due month will be 2024-04-30.

4

Step 4: Copy the formula in D2 down to all rows to find the last day of each due month.

Drag the fill handle from D2 down to D7.

Expected Result

The last day of the due month is shown for all invoices.

Final Result

Invoice ID | Start Date | Due Date (3 months later) | End of Due Month
-----------|------------|--------------------------|-----------------
INV001     | 2024-01-15 | 2024-04-15               | 2024-04-30      
INV002     | 2024-02-10 | 2024-05-10               | 2024-05-31      
INV003     | 2024-03-05 | 2024-06-05               | 2024-06-30      
INV004     | 2024-04-20 | 2024-07-20               | 2024-07-31      
INV005     | 2024-05-25 | 2024-08-25               | 2024-08-31      
INV006     | 2024-06-30 | 2024-09-30               | 2024-09-30      

✓The due date is exactly 3 months after the invoice start date.

✓The end of due month shows the last calendar day of the month when payment is due.

✓This helps the manager track invoice deadlines and month-end closing dates.

Bonus Challenge

Create a new column that shows the number of days between the start date and the end of the due month.

Show Hint

Use the formula =D2 - B2 to subtract the start date from the end of due month.