Single Cell Formula to Create Fiscal Period Calendar Table in Excel Worksheet

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to use single cell formula to create fiscal period calendar table in Excel worksheet. Let’s get started

Video

Sample Data

From the screenshot below, we’ve got date that spanned from 01/01/2020 to 31/12/2013 in column A.

Single Cell Formula to Create the Calendar Table

Although, we can create the calendar table in the PowerBI Desktop and create a one to many relationships with the fact table. We want to use the Excel formula to create the same Calendar table. To do that:

• In cell B2, execute this formula:

=LET(dimDate,A2:A5114,Year,YEAR(dimDate),Month_Number,MONTH(dimDate),Month,TEXT(dimDate,”mmm”),Quarter,ROUNDUP(Month_Number/3,0),FiscalQuarter,IF(Quarter=1,4,Quarter-1),
FiscalYear,IF(Quarter=1,Year-1,Year),FiscalPeriod,”Q”&FiscalQuarter&”-“&FiscalYear,Calculation,HSTACK(Year,Month_Number,Month,Quarter,FiscalQuarter,FiscalYear,FiscalPeriod),Calculation)

• Click Enter

Formula Explanation

1. LET: The LET function allows you to define variables within a formula, making it easier to manage and understand complex calculations.
2. dimDate: This variable references the range of cells A2:A5114, which likely contains dates.
3. Year: This variable calculates the year for each date in the dimDate range using the YEAR function.
4. Month_Number: This variable calculates the month number for each date in the dimDate range using the MONTH function.
5. Month: This variable converts each date in the dimDate range to a three-letter abbreviation for the month using the TEXT function with the “mmm” format.
6. Quarter: This variable calculates the quarter number for each date in the dimDate range by dividing the Month_Number by 3 and rounding up to the nearest integer using the ROUNDUP function.
7. FiscalQuarter: This variable adjusts the Quarter value to represent the fiscal quarter. If the Quarter is 1, it sets FiscalQuarter to 4; otherwise, it subtracts 1 from the Quarter.
8. FiscalYear: This variable adjusts the Year value to represent the fiscal year. If the Quarter is 1, it sets FiscalYear to Year - 1; otherwise, it keeps the Year as it is.
9. FiscalPeriod: This variable combines the fiscal quarter and fiscal year values into a string with the format “Q-FiscalYear,” e.g., “Q1-2023.”
10. Calculation: This variable stacks all the previously calculated values (Year, Month_Number, Month, Quarter, FiscalQuarter, FiscalYear, and FiscalPeriod) into a single row using the HSTACK function.
11. The formula finally returns the Calculation variable as the result of the entire expression.

The purpose of this formula is transforming a range of dates in column A (A2:A5114) into a summarized table of information related to each date. The table have columns showing the year, month number, month abbreviation, quarter number, fiscal quarter number, fiscal year, and fiscal period for each date in the range. This formula can be helpful for organizing and analyzing time-based data, especially when dealing with fiscal periods that don’t align with calendar months.

See you in the next tutorial