Summary  
This chapter demonstrates how to decompose dates and use dynamic date functions (MONTH, YEAR, TEXT, TODAY, EOMONTH) along with conditional aggregation functions (SUMIFS, COUNTIFS) to build automatically updating time‐based metrics like current month, previous month, and year-to-date.

General domain of usage  
Business performance reporting



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.

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?