EDATE and EOMONTH in Google Sheets - Dashboard Guide

Dashboard Mode - EDATE and EOMONTH

Goal

Find the due dates and month-end dates for invoices based on their invoice dates and payment terms.

Sample Data

Invoice ID	Invoice Date	Payment Terms (Months)
INV001	2024-01-15	1
INV002	2024-02-20	3
INV003	2024-03-05	6
INV004	2024-04-10	0
INV005	2024-05-25	2

Dashboard Components

• KPI Table: Shows Invoice ID, Invoice Date, Payment Terms, Due Date, and Month-End Due Date.
• Formulas:
  • Due Date = =EDATE(B2, C2) (Adds payment terms months to invoice date)
  • Month-End Due Date = =EOMONTH(B2, C2) (Finds last day of due month)

Invoice ID	Invoice Date	Payment Terms (Months)	Due Date (EDATE)	Month-End Due Date (EOMONTH)
INV001	2024-01-15	1	=EDATE(B2,C2) -> 2024-02-15	=EOMONTH(B2,C2) -> 2024-02-29
INV002	2024-02-20	3	=EDATE(B3,C3) -> 2024-05-20	=EOMONTH(B3,C3) -> 2024-05-31
INV003	2024-03-05	6	=EDATE(B4,C4) -> 2024-09-05	=EOMONTH(B4,C4) -> 2024-09-30
INV004	2024-04-10	0	=EDATE(B5,C5) -> 2024-04-10	=EOMONTH(B5,C5) -> 2024-04-30
INV005	2024-05-25	2	=EDATE(B6,C6) -> 2024-07-25	=EOMONTH(B6,C6) -> 2024-07-31

Dashboard Layout

+-------------------------------------------------+
|                 Invoice Due Dates                |
+-------------------------------------------------+
| Invoice Table with:                              |
| - Invoice ID                                    |
| - Invoice Date                                  |
| - Payment Terms (Months)                        |
| - Due Date (EDATE)                             |
| - Month-End Due Date (EOMONTH)                 |
+-------------------------------------------------+

Interactivity

Add a filter to select invoices by Payment Terms (Months). When you choose a specific number of months, the table updates to show only invoices with that payment term. This helps focus on invoices due in certain time frames.

Self Check

If you add a filter for Payment Terms = 1, which invoices appear and what are their Due Dates and Month-End Due Dates?

Answer: Only INV001 appears with Due Date 2024-02-15 and Month-End Due Date 2024-02-29.

Key Result

Dashboard shows invoice due dates and month-end due dates using EDATE and EOMONTH formulas.