Excelspreadsheet~15 mins

EDATE and EOMONTH in Excel - Real Business Scenario

Choose your learning style9 modes available

Learn Why Deep Sheet Try Challenge Scenario Recall Dash

Scenario Mode

👤 Your Role: You are a financial analyst at a company that manages subscription services.

📋 Request: Your manager wants you to calculate the next billing date and the last day of the billing month for each customer based on their subscription start date.

📊 Data: You have a list of customers with their subscription start dates and the number of months between each billing cycle.

🎯 Deliverable: Create a table that shows for each customer the next billing date and the last day of the billing month using EDATE and EOMONTH formulas.

Progress0 / 4 steps

Sample Data

Customer	Start Date	Billing Cycle (Months)
Alice	2024-01-15	1
Bob	2024-02-20	3
Charlie	2024-03-05	6
Diana	2024-04-10	12
Eva	2024-05-25	1

Step 1: Create a new column named 'Next Billing Date'. In the first cell under this column (for Alice), enter the formula to add the billing cycle months to the start date using EDATE.

=EDATE(B2, C2)

Expected Result

Step 2: Copy the formula from the first 'Next Billing Date' cell down to all other customers to calculate their next billing dates.

Copy =EDATE(B2, C2) down the column

Expected Result

Step 3: Create another new column named 'End of Billing Month'. In the first cell under this column (for Alice), enter the formula to find the last day of the month of the next billing date using EOMONTH.

=EOMONTH(D2, 0)

Expected Result

Step 4: Copy the formula from the first 'End of Billing Month' cell down to all other customers to calculate the last day of their billing months.

Copy =EOMONTH(D2, 0) down the column

Expected Result

Final Result

Customer | Start Date | Billing Cycle (Months) | Next Billing Date | End of Billing Month
-------- | ---------- | --------------------- | ----------------- | --------------------
Alice    | 2024-01-15 | 1                     | 2024-02-15        | 2024-02-29           
Bob      | 2024-02-20 | 3                     | 2024-05-20        | 2024-05-31           
Charlie  | 2024-03-05 | 6                     | 2024-09-05        | 2024-09-30           
Diana    | 2024-04-10 | 12                    | 2025-04-10        | 2025-04-30           
Eva      | 2024-05-25 | 1                     | 2024-06-25        | 2024-06-30

✓EDATE helps add months to a date to find the next billing date easily.

✓EOMONTH finds the last day of the month for any given date, useful for billing period ends.

✓This table helps the billing team know exactly when to charge customers and when the billing month ends.

Bonus Challenge

Add a column to calculate the number of days from today until the next billing date.

Show Hint