Cat On A Spreadsheet

Dates look deceptively simple in a transaction table. Each record happened on a particular day, Excel stores that day as a number, and functions such as YEAR, MONTH and EOMONTH make extracting information from it straightforward. Reporting requirements are rarely expressed at that level, however. Management does not usually ask for a list of transactions that occurred on 17 March. It asks for March performance, the current quarter, year-to-date results, the last twelve months, growth against the previous month or the equivalent period last year. So while the source contains dates, the report needs periods. That means the first task is not aggregation at all. It is translating individual dates into the reporting periods they belong to.

Suppose we have the same tblTransactions table used in the previous post, containing Date, CustomerID, Department, Status and Value. This time, rather than producing a customer-level report for one selected month, we want to build a monthly performance report from the transaction history. One tempting approach is to extract the month number with MONTH. For a single date, that works perfectly. Applied to an array, MONTH(tblTransactions[Date]) returns the month number for every transaction. But month number alone is not a suitable reporting key. January 2025 and January 2026 both produce 1. If our transaction history spans several years, grouping by that result would combine periods that should remain separate.

We could combine YEAR and MONTH, perhaps producing text such as "2026-01", but there is usually a better option: represent the reporting month with an actual date. For example, EOMONTH(tblTransactions[Date],0) converts every transaction date into the final day of its month. All transactions during September 2026 therefore become 30 September 2026. Alternatively, we can derive the first day of each month with EOMONTH(tblTransactions[Date],-1)+1. Either works. The important point is that the result remains a real Excel date. That gives us something that can be sorted chronologically, compared mathematically, formatted however we want and used directly in later date calculations. We can display Sep 2026 while the underlying value remains 1 September 2026. This is generally preferable to turning the period into text too early.

With that decision made, a monthly summary becomes quite straightforward:

There are two different date arrays here, and the distinction matters. TransactionMonths has the same number of rows as the filtered transaction dataset. Each transaction has been assigned to a reporting month. Months, by contrast, contains one row per distinct reporting month.

We have changed the grain again. This is the same pattern we used when converting transaction data into a customer report. The difference is that the grouping dimension did not exist explicitly in the source. We derived it from another field first. Once Months exists, we can calculate several measures against it. Transaction count can be produced with COUNTIF(TransactionMonths,Months), while average transaction value could either be calculated with AVERAGEIF or, more efficiently once count and total already exist, by dividing MonthlyValue by TransactionCount.

A fuller report might therefore look like this:

If the first column is formatted as mmm yyyy, we now have a conventional monthly management report without ever converting the month into text inside the calculation.

There is, however, a problem hiding in this approach. UNIQUE(TransactionMonths) returns only months that actually occur in the data. Suppose there were transactions in January, February, April and May, but none in March. March disappears from the report entirely. That may be exactly what we want when analysing categories or customers. A customer with no activity might reasonably be absent from an activity report. However, time is different. If we are showing a monthly trend, the absence of March usually means March should appear with zero activity, not that the calendar jumped directly from February to April.

This is where generating the reporting dimension becomes preferable to extracting it from the source. If H2 contains the first month we want to report and H3 contains the last, we can generate every month between them using SEQUENCE and EDATE. The number of months is DATEDIF(StartMonth,EndMonth,"m")+1, and EDATE(StartMonth,SEQUENCE(NumberOfMonths,1,0)) can then generate the complete sequence. Used inside the report, that gives us:

Now the reporting calendar exists independently of the transaction data.

That is a significant change in design. Previously, we asked the source data which months existed. Now we define which months the report should contain and ask the transaction data what happened during each one. The distinction becomes particularly valuable when there is no activity. March still exists because March belongs to the reporting calendar, regardless of whether there were any transactions.

This is essentially a lightweight version of a calendar dimension, something that becomes much more explicit when working with the Excel Data Model or Power BI. Even in a worksheet formula, however, the principle is useful: time can be treated as its own analytical structure rather than merely as a field attached to transactions.

Once we have a chronological monthly array, comparisons between periods become possible. Suppose management wants month-on-month change. If MonthlyValue contains one value for each month in chronological order, we need to compare each value with the value immediately before it. DROP is particularly convenient here. DROP(MonthlyValue,1) removes the first month, while DROP(MonthlyValue,-1) removes the last. The resulting arrays have the same size and represent current and previous periods respectively. The differences can therefore be calculated directly:

If monthly values are 100, 130, 120 and 180, the two arrays effectively become 130, 120, 180 and 100, 130, 120. Subtraction then gives 30, -10 and 60. The first month has no previous month within the reporting array, so there is no legitimate month-on-month comparison for it.

That presents a structural question. Our change array has one fewer row than our month array. We could simply omit the first month from the comparison report, or we could deliberately add a placeholder so that the arrays remain aligned. For a management report, retaining the month is often preferable. VSTACK(NA(),Current-Previous) can place #N/A in the first row and the calculated changes beneath it.

Percentage change introduces another familiar complication. The mathematical expression is (Current-Previous)/Previous, but if Previous is zero, the percentage change is undefined. We should decide what that means rather than allowing the formula to make the decision accidentally. If zero represents a genuine absence of activity, "New" may sometimes be more informative than an error. In another report, NA() may be preferable because it prevents the value from being interpreted as a normal percentage. The correct treatment depends on what the measure is intended to communicate.

This becomes even more important when we move from month-on-month comparison to year-on-year comparison. If our report contains a continuous monthly calendar, one approach is to retrieve the value belonging to the same month one year earlier. Because Months contains genuine dates, the corresponding previous-year period is simply EDATE(Months,-12). We can then use XLOOKUP to retrieve the previous-year values:

This is one of the reasons we retained real dates as our period keys. Had we converted months into display text such as "Sep 2026", calculating the corresponding prior-year period would require us to reconstruct date logic from text. With actual dates, moving twelve months backwards is a natural operation.

The same principle applies to quarters. There is no QUARTER function required to solve the problem. A quarter can be derived from the month number. The quarter number for a date is ROUNDUP(MONTH(Date)/3,0). But, just as month number alone was insufficient across multiple years, quarter number alone is not a complete reporting key. Q1 2025 and Q1 2026 are different periods. We can instead derive a real date representing the start of the quarter.

The starting month of a quarter can be calculated from the transaction month, and from there a proper date can be constructed. Once every transaction has a QuarterStart value, the same pattern we used for months applies again: identify or generate the quarters, aggregate against them, then format the resulting dates for presentation.

This reveals something useful about time intelligence in Excel. Months, quarters and years may appear to require completely different reporting formulas, but the underlying pattern is often identical. First, derive a period key from the transaction date. Then establish the distinct or required reporting periods. Finally, aggregate the transaction measures against those periods. The period definition changes, but the reporting architecture does not.

Year-to-date reporting introduces a slightly different requirement because it does not simply group transactions. It defines a moving population. Suppose H2 contains the reporting date. The start of the reporting year can be derived with DATE(YEAR(H2),1,1). The valid YTD population consists of transactions whose dates are greater than or equal to that date and less than or equal to H2. That sounds straightforward, and technically it is. The more interesting question arises when management asks for comparison with the previous year. Should “previous YTD” mean the whole of the previous year? Usually not.

If the current reporting date is 28 September 2026, comparing January-to-September 2026 against January-to-December 2025 would create a misleading comparison. The corresponding previous-year period normally ends on 28 September 2025. Because the reporting date is a genuine date value, EDATE(H2,-12) gives us that comparison boundary directly.

This is where time intelligence stops being merely a matter of extracting month names. The definition of the comparison period is part of the business logic.

The same issue appears with rolling periods. A “last 12 months” report might mean the twelve complete calendar months before the current month, or it might mean the 12-month period ending today. Those are not the same population. If the report is run on 28 September, a twelve-complete-month period might run from 1 September of the previous year through 31 August of the current year. A literal trailing twelve-month period might instead run from 29 September of the previous year through 28 September of the current year. Neither definition is intrinsically correct. The requirement needs to specify which one is intended.

Once that definition exists, however, dynamic-array filtering makes implementation relatively direct. The difficult part is often not writing the FILTER expression. It is defining the period accurately enough that the formula answers the right question.

This is one reason dates deserve more attention in reporting than they usually receive. A transaction date looks like an ordinary column, but it contains the basis for an entire reporting dimension: day, week, month, quarter, year, financial period, year-to-date, rolling period and prior-period comparisons can all be derived from it.

There is also no requirement that the business calendar match the normal calendar. If an organisation's financial year begins in April, the definition of quarter changes. April through June becomes Q1, July through September becomes Q2, and so on. The same dynamic-array approach still works, but the transformation from transaction date to reporting period needs to reflect that calendar.

This is another example of why the source data should not dictate the report's analytical structure. The source tells us that a transaction occurred on 15 May 2026. Whether that belongs to calendar Q2, financial Q1, period 2 of a custom accounting calendar or week 20 is a reporting decision. The date itself does not contain that business meaning. We add it through transformation.

For irregular accounting calendars, particularly 4-4-5 calendars or organisations with specially defined accounting periods, trying to encode every rule into increasingly elaborate date arithmetic may not be the best design. A separate calendar table containing every date and its associated reporting attributes is often cleaner. That calendar might contain the date, financial year, financial quarter, accounting period and week number. At that point, time enrichment becomes another lookup problem. The transaction date is the key, and the calendar table provides the reporting attributes. The techniques from earlier posts therefore return in a slightly different form. We enrich transactions with a reporting period, group them by that period, aggregate the required measures and compare the resulting period-level arrays.

This is what makes the dynamic-array approach useful beyond individual formulas. The same small collection of transformations keeps reappearing in different reporting problems: filter, derive, enrich, group, aggregate, compare, reshape. The functions change depending on the data, but the structure remains remarkably consistent. And time reporting demonstrates something else particularly well: a good report does not simply summarise the records that happen to exist. It imposes an analytical structure on them.

Our transaction table knew nothing about missing months, reporting quarters, year-to-date boundaries or prior-year comparisons. Those concepts belonged to the report, so we constructed them. That gives us a useful foundation for the next real-world problem.

So far, most of our reports have produced a single result from a single set of controls. Choose a department and period, and the formula produces the corresponding report. In practice, users often want something more interactive. They want to choose several criteria, leave some criteria unrestricted, search for partial text, set minimum and maximum values, and have all of those selections work together without maintaining a different formula for every possible combination. That takes us from time intelligence to another common reporting challenge: building a dynamic report with optional, user-controlled criteria.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet