Creating Time-Based Structures

Forecasting is fundamentally about time. To build moving averages, detect seasonality, group data by period, and create multi-year trend charts, you need clean time dimensions.

Essential Date-Extraction Formulas

All of these work from the Order_Date column (column B):

=YEAR(B2)                    → extracts the year (2022, 2023, 2024)
=MONTH(B2)                   → extracts the month number (1–12)
=DAY(B2)                     → extracts the day of the month
=TEXT(B2,"MMMM")             → full month name ("August")
=TEXT(B2,"MMM")              → abbreviated month name ("Aug")
=WEEKNUM(B2)                 → week number of the year (1–53)
=WEEKDAY(B2,2)               → day of week (1=Monday, 7=Sunday)
=EOMONTH(B2,0)               → last day of the order's month
=DATE(YEAR(B2),MONTH(B2),1)  → first day of the order's month

The last one — the Month_Start date — is the single most useful time column you'll build. When you aggregate revenue by it, Excel's charts automatically create clean monthly trend lines.

Building a Month_Start Column

In a new column (column V), add the header Month_Start and enter:

=DATE(YEAR(B2),MONTH(B2),1)

Autofill down all 1,500 rows, then format the column as a date (Ctrl+1 → Date → MMM-YY style). Every row now has a groupable, sortable monthly anchor that PivotTables and SUMIFS can work with directly.

Creating Rolling Period Labels

Rolling labels let you flag and filter the most recent N months dynamically, without manually updating ranges every month:

=TEXT(B2,"YYYY-MM")   → sortable period label: "2022-08"

Rolling 12-month flag:

=IF(B2>=EDATE(TODAY(),-12),"In","Out")

Rolling 3-month flag:

=IF(B2>=EDATE(TODAY(),-3),"In","Out")

Year-to-date flag:

=IF(AND(YEAR(B2)=YEAR(TODAY()),B2<=TODAY()),"YTD","")

Building a Calendar Table

A Calendar Table is a standalone lookup table listing every month in your dataset alongside its Year, Quarter, Month Number, and period labels. It acts as the time dimension backbone for all PivotTable relationships and SUMIFS formulas — rather than deriving these values repeatedly inside each formula, you look them up once from a single authoritative source.

Create a new sheet called Calendar_Table with the following structure:

For Period_Date, enter 2022-01-01 in the first row, then use =EDATE(A2,1) in every row below and autofill down to December 2026 — that gives you 60 rows covering all historical data plus a 2-year forecast runway.

Task 1: Build Time-Based Helper Columns

Goal: Add the time dimensions the forecasting models in later sections depend on.

1. In column V of your data sheet, add the header Month_Start and enter =DATE(YEAR(B2),MONTH(B2),1). Autofill down all 1,500 rows and format the column as MMM-YY.
2. In column W, add the header Period_Label and enter =TEXT(B2,"YYYY-MM"). This will be used as a chart axis label and PivotTable grouping key.
3. In column X, add the header Rolling_12M and enter =IF(B2>=EDATE(TODAY(),-12),"In","Out") to flag the most recent 12 months.
4. In a blank cell, use =COUNTIF(X2:X1501,"In") to count how many rows fall inside the rolling window — note the number for later reference.
5. Add one more column Y with the header YTD_Flag and enter =IF(AND(YEAR(B2)=MAX(YEAR(Business_Data[Order_Date])),MONTH(B2)<=MONTH(TODAY())),"YTD",""). This flags rows from the most recent year in the dataset (2024) whose month falls on or before the current month — simulating a YTD view as if the data were live.

Task 2: Create the Calendar Table

Goal: Build the shared time dimension that all forecast models will reference.

1. Insert a new sheet and name it Calendar_Table.
2. In cell A1, type Period_Date. In cell A2, enter 2022-01-01. In A3, enter =EDATE(A2,1) and autofill down to row 61 — that covers January 2022 through December 2026.
3. Select column A and go to Home → Number Format dropdown. Choose Short Date.
4. Add columns B through H using the formulas from the table above (Year, Quarter_Num, Quarter_Label, Month_Num, Month_Name, Month_Short, Period_Label).
5. Format the header row with a dark fill and white bold text to distinguish it visually.
6. Select the full range A1:H61 and press Ctrl+T to convert it to an Excel Table. Name the table Calendar_Table in the Table Design tab.
7. Verify the table by checking that row 38 shows 2025-01-01 in Period_Date, 2025 in Year, and Q1 in Quarter_Label.

A completed workbook is attached for comparison and troubleshooting if needed.