4-4-5 Fiscal Year, Need Formula to Identify Quarters

Rossana

Hello,

I need some help developing a formula to calculate the fiscal year now that my company has switched to a 4-4-5 fiscal year calendar.

The new fiscal year used to start in October and this was my formula:

=IF(MONTH(Date@row) <= 3, "Q2", IF(MONTH(Date@row) <= 6, "Q3", IF(MONTH(Date@row) <= 9, "Q4", "Q1")))

How would I adapt this to a 4-4-5 fiscal calendar year? Do I count by months and weeks? Days? The fiscal year started September 27th this year.

Any insight is appreciated.

Thank you!

Philip Robbins

@Rossana I dropped your question into Claude and it came back with the following. I put the formulas into a sheet and they all check out. The only change I made is that I added a Sheet Summary date field for the start of the year, to avoid hard coding the date.

Simple version (single fiscal year, start 27 Sept 2026)

Fiscal week:

=INT((Date@row - DATE(2026, 9, 27)) / 7) + 1

Quarter:

="Q" + MIN(4, INT((INT((Date@row - DATE(2026, 9, 27)) / 7)) / 13) + 1)

The MIN(4, ...) handles 53-week years, where the extra week would otherwise return "Q5".

Fiscal period (1 to 12), if you need it, using a helper column Fiscal Week:

=(INT(([Fiscal Week]@row - 1) / 13)) * 3 + IF(MOD([Fiscal Week]@row - 1, 13) < 4, 1, IF(MOD([Fiscal Week]@row - 1, 13) < 8, 2, 3))

Handling multiple years

Hardcoding the start date only works for one year. Also, 4-4-5 calendars add a 53rd week roughly every 5 to 6 years, so the start dates are not a clean 364 days apart. The robust approach:

1. Create a reference sheet with one row per fiscal year, with a FY Start date column (27/09/2026, then whatever your finance team publishes for later years).
2. In your main sheet, add a helper column FY Start with:

=MAX(COLLECT({Ref FY Start}, {Ref FY Start}, <=Date@row))

1. Then use [FY Start]@row in place of DATE(2026, 9, 27) in the formulas above.

Check with your finance team that weeks start on Sunday (27 Sept 2026 is a Sunday) and where they put the 53rd week in a long year, as the quarter logic above assumes it falls in Q4.