Summary  
Demonstrates dynamic time-based data aggregation by decomposing dates with MONTH and YEAR, using TODAY and EOMONTH for relative date references, and applying SUMIFS/COUNTIFS criteria to compute current‐month, previous‐month, and year-to-date metrics that update automatically.  

General domain of usage  
Business performance tracking



Time-based reporting requires more than raw transaction dates. In this chapter, build dynamic monthly and year-to-date analytics that update automatically based on the current date.

## Date Decomposition Functions

```excel id="1"
=MONTH(serial_number)
=YEAR(serial_number)
=TEXT(value, format_text)
```

* `MONTH()`: returns the month number;
* `YEAR()`: returns the year;
* `TEXT()`: formats dates into readable labels.



## TODAY Structure

```excel id="4"
=TODAY()
```

`TODAY()` returns the current system date dynamically.

## EOMONTH Structure

```excel id="5"
=EOMONTH(start_date, months)
```

* `start_date`: starting date;
* `months`: number of months shifted forward or backward.

Formula used in this chapter:

```excel id="6"
=EOMONTH(TODAY(),-1)
```

This safely returns a valid date from the previous month, even across year boundaries.





Confirm that `Sales_Data[Date]` contains valid Excel date values instead of text strings.



Step 1 Validate the Date Column


Inside `Sales_Data`, create the following columns:

```text id="7"
Month
Year
Month_Year
```

Inside the first row of each column, type:

```excel id="8"
=MONTH([@Date])
=YEAR([@Date])
=TEXT([@Date],"MMM YYYY")
```

The dataset now contains reusable time dimensions for analysis.



Step 2 Create Helper Columns


Type:

```excel id="11"
=SUMIFS(Sales_Data[Revenue],Sales_Data[Month],MONTH(TODAY()),Sales_Data[Year],YEAR(TODAY()))
```

* `MONTH(TODAY())`: current month;
* `YEAR(TODAY())`: current year.

The KPI now updates automatically as the calendar changes.



Step 3 Build Current Month Revenue


Type:

```excel id="12"
=COUNTIFS(Sales_Data[Month],MONTH(TODAY()),Sales_Data[Year],YEAR(TODAY()))
```

This counts all transactions from the current month and year.



Step 4 Build Current Month Order Count


Type:

```excel id="13"
=SUMIFS(Sales_Data[Revenue],Sales_Data[Month],MONTH(EOMONTH(TODAY(),-1)),Sales_Data[Year],YEAR(EOMONTH(TODAY(),-1)))
```

* `EOMONTH(TODAY(),-1)`: previous month reference;
* `MONTH(...)`: previous month number;
* `YEAR(...)`: correct year context.

The formula remains stable across month and year transitions.



Step 5 Build Previous Month Revenue


Type:

```excel id="14"
=SUMIFS(Sales_Data[Revenue],Sales_Data[Year],YEAR(TODAY()),Sales_Data[Month],"<="&MONTH(TODAY()))
```

* `"<="&MONTH(TODAY())`: dynamic comparison operator;
* Includes all months up to the current month.



Step 6 Build Year-to-Date Revenue


Add a transaction dated within the current month.

Confirm that:

* Current month Revenue updates automatically;
* Current month order count updates automatically;
* Year-to-date Revenue updates automatically.



Step 7 Test Time Sensitivity

Why do we split dates into Month and Year helper columns?

Why is `EOMONTH` preferred over subtracting `1` from `MONTH(TODAY())`?

What does the expression `"<="&MONTH(TODAY())` allow in YTD calculations?

Build a complete Excel business analysis system using modern formulas, dynamic reporting, financial modeling, and scenario-based analysis. Throughout the course, you will transform raw transactional data into interactive dashboards, KPI engines, and scalable analytical models that reflect real-world business workflows.

Build the core Excel skills used in real business reporting workflows. Work with formulas, lookups, logical functions, conditional aggregation, error handling, and data cleaning while developing a structured sales analysis model.


Build dynamic Excel reporting systems using modern array functions and formula-driven workflows. Work with FILTER, SORT, SORTBY, UNIQUE, TRANSPOSE, spill ranges, interactive inputs, ranking systems, validation lists, and chart-ready data structures that update automatically as the dataset grows.


Build scalable relational Excel models using advanced lookup patterns and parameter-driven logic. Work with XLOOKUP, INDEX, MATCH, XMATCH, approximate matching, dynamic table relationships, scenario-based calculations, category-level aggregation, and flexible lookup architectures used in real analytical workflows.


Build advanced analytical and financial models that respond dynamically to changing data and assumptions. Work with time-based calculations, dynamic ranges, Named Ranges, installment modeling, investment analysis, amortization schedules, weighted aggregations, and scenario-driven financial metrics used in real business reporting systems.

Transform the workbook into a fully interactive business simulation system driven by scenarios, sensitivity analysis, and dynamic KPI logic. Build scenario engines, parameter-driven dashboards, sensitivity grids, advanced text-processing formulas, and centralized reporting layers that respond instantly to user inputs and business assumptions.

Time-Based Analysis

Date Decomposition Functions

TODAY Structure

EOMONTH Structure

1. Why do we split dates into Month and Year helper columns?

2. Why is `EOMONTH` preferred over subtracting `1` from `MONTH(TODAY())`?

3. What does the expression `"<="&MONTH(TODAY())` allow in YTD calculations?