EDATE and EOMONTH in Excel - Dashboard Guide

Dashboard Mode - EDATE and EOMONTH

Dashboard Goal

Track subscription start dates and calculate renewal dates and billing cycle ends easily.

Sample Data

Customer	Subscription Start	Months to Add
Alice	2024-01-15	3
Bob	2024-02-28	6
Charlie	2024-03-31	1
Diana	2024-04-10	12
Eva	2024-05-20	2

Dashboard Components

• Renewal Date (Column D):
  =EDATE(B2, C2)
  This adds the number of months to the subscription start date to find the renewal date.
• Billing Cycle End (Column E):
  =EOMONTH(B2, C2 - 1)
  This finds the last day of the month after adding the months, showing the billing cycle end date.
• Summary Table: Shows total subscriptions and average months to add.
  =COUNTA(A2:A6) for total subscriptions.
  =AVERAGE(C2:C6) for average months.

Dashboard Layout

+----------------------+----------------------+----------------------+
|      Sample Data     |    Renewal Dates     | Billing Cycle Ends   |
|  (Table with A-C)    |  (Column D formulas) |  (Column E formulas) |
+----------------------+----------------------+----------------------+
|                      |                      |                      |
|  Summary Table Below  (Total Subs, Avg Months)                  |
+---------------------------------------------------------------+

Interactivity

Filter by Customer name or Subscription Start month to update the Summary Table and visible rows in the data. Renewal Dates and Billing Cycle Ends update automatically based on filtered data.

Self Check

If you filter to show only subscriptions starting in March 2024, which customers remain and what are their renewal dates?

Key Result

Dashboard calculates subscription renewal dates and billing cycle ends using EDATE and EOMONTH.