Fiscal Year Starts in February sometimes end of January

Adriane Price

Our fiscal years (FY), quarters (Q) and work weeks (WW) are giving me heartburn. I realize I need to create a table in order to capture each fiscal year. However, when putting in the formulas so far I am seeing issues with my Work Weeks and Fiscal Year columns.

Part one (1) of my heartburn. For example, my work week is showing WW0 when it should be displaying WW1 for the first week of the new FY and Q.

These are the two formulas I have tried using.

Work Week Column Formula used: ="WW" + IF((WEEKNUMBER([Quarter Start Date]@row) - 5) < 0, WEEKNUMBER([Quarter Start Date]@row) + 52, WEEKNUMBER([Quarter Start Date]@row) - 5)

And/Or

Work Week Column Formula used: ="WW" + IF((WEEKNUMBER([Quarter Start Date]@row) - 5) < 0, WEEKNUMBER([Quarter Start Date]@row) + 48, WEEKNUMBER([Quarter Start Date]@row) - 5)

And both of the formula provides the same output.

When I use work week column formula: ="WW" + IF(WEEKNUMBER([Quarter Start Date]@row, 2) = 4, 52, WEEKNUMBER([Quarter Start Date]@row, 2) - 4)

It displays correctly for FY24 - FY27 but for FY28 it is incorrect work week.

Then part two (2) of my issue is fiscal year column there are two sections it is displays the incorrect fiscal year for January 31, 2026 (should be FY27) and January 30, 2027 (should be FY28).

This is the fiscal calendar we use and I am attempting to incorporate the same into Smartsheet for our org's reports.

FiscalCalendar.xlsx

Fiscal Year Column Formula used:

=IF(Month@row > 1, (IF(ISDATE([Quarter Start Date]@row), IF(YEAR([Quarter Start Date]@row) = 2020, "FY21", IF(YEAR([Quarter Start Date]@row) = 2021, "FY22", IF(YEAR([Quarter Start Date]@row) = 2022, "FY23", IF(YEAR([Quarter Start Date]@row) = 2023, "FY24", IF(YEAR([Quarter Start Date]@row) = 2024, "FY25", IF(YEAR([Quarter Start Date]@row) = 2025, "FY26", IF(YEAR([Quarter Start Date]@row) = 2026, "FY27", IF(YEAR([Quarter Start Date]@row) = 2027, "FY28", IF(YEAR([Quarter Start Date]@row) = 2028, "FY29", IF(YEAR([Quarter Start Date]@row) = 2029, "FY30", IF(YEAR([Quarter Start Date]@row) = 2030, "FY31", IF(YEAR([Quarter Start Date]@row) = 2031, "FY32")))))))))))))), (IF(ISDATE([Quarter Start Date]@row), IF(YEAR([Quarter Start Date]@row) = 2020, "FY20", IF(YEAR([Quarter Start Date]@row) = 2021, "FY21", IF(YEAR([Quarter Start Date]@row) = 2022, "FY22", IF(YEAR([Quarter Start Date]@row) = 2023, "FY23", IF(YEAR([Quarter Start Date]@row) = 2024, "FY24", IF(YEAR([Quarter Start Date]@row) = 2025, "FY25", IF(YEAR([Quarter Start Date]@row) = 2026, "FY26", IF(YEAR([Quarter Start Date]@row) = 2027, "FY27", IF(YEAR([Quarter Start Date]@row) = 2028, "FY28", IF(YEAR([Quarter Start Date]@row) = 2029, "FY29", IF(YEAR([Quarter Start Date]@row) = 2030, "FY30", IF(YEAR([Quarter Start Date]@row) = 2031, "FY31")))))))))))))))

I can provide access to my Fiscal Lookup in Smartsheet if needed.

What I am trying to figure out:

1) is there a way to make the Fiscal Year (FY) formula more condensed with this odd type of fiscal year calendar?

2) Where did I go wrong in the Work Week formula that it is not calculating correctly?

3) Why is my Fiscal Year formula not pulling in the correct fiscal years for some "quarter start date"?

Thought: Maybe I need to create a Fiscal lookup for just FY24 - FY26 then another one for FY27 - FY29 basically every two years or make it so that it is not a column formula. I was attempting to make my work or anyone else who may take over this less manual.

Thank you!

Paul Newcome

Right off I can help consolidate your FY formula and make it much more scalable so that you should never need to change it based on the year.

=IF(ISDATE([Quarter Start Date]@row), "FY" + RIGHT(YEAR([Quarter Start Date]@row) + IF(MONTH([Quarter Start Date]@row)> 1, 1, 0), 2)

This also helps to explain why you are getting an unexpected output for those January dates. Your formula is only adding one to the year when the month is greater than 1 (meaning February forward). Since January is not greater than January, it is not adding 1 to the year.

It also looks like your work weeks are going from Saturday to Friday, but the WEEKNUMBER function is based on Sunday to Saturday, and it looks like you are setting your fiscal calendar to 52 (sometimes 53) weeks in a year as opposed to the months, so that also presents a challenge. Depending on they year, Feb 1 could be Week 52, Week 53, or Week 1.

My suggestion:

Create a table with a date type column that has the start date of each fiscal year in it. Since you don't need to enter every week of every year, you can drag this down to cover many many years.

Then you can leverage this FY Start Date in the rest of your calculations. The base formula to pull in the appropriate FY Start Date based on the [Quarter Start Date]:

=IFERROR(MAX(COLLECT({FY Start Date}, {FY Start Date}, @cell<= [Quarter Start Date]@row)), "")

And the FY End Date can be calculated based off of the next start date minus one day:

=IFERROR(MIN(COLLECT({FY Start Date}, {FY Start Date}, @cell> [Quarter Start Date]@row)) - 1, "")

Now we can use these in other formulas like your FY formula:

=IF(ISDATE([Quarter Start Date]@row), "FY" + RIGHT(IFERROR(MAX(COLLECT({FY Start Date}, {FY Start Date}, @cell<= [Quarter Start Date]@row)), ""), 2)

I've gotta log off for now though, so I will revisit the week numbers when I get back in on Monday unless someone else steps in in the meantime.

Adriane Price

@Paul Newcome - Thank you for responding. I plugged in the Fiscal Year formula and all was good until January 2026, 2027 and 2028

Using Formula: =IF(ISDATE([Quarter Start Date]@row), "FY" + RIGHT(YEAR([Quarter Start Date]@row) + IF(MONTH([Quarter Start Date]@row) > 1, 1, 0), 2))

"It also looks like your work weeks are going from Saturday to Friday, but the WEEKNUMBER function is based on Sunday to Saturday, and it looks like you are setting your fiscal calendar to 52 (sometimes 53) weeks in a year as opposed to the months, so that also presents a challenge. Depending on they year, Feb 1 could be Week 52, Week 53, or Week 1."

In our company scenario our our fiscal work week runs from Saturday to Friday

"Create a table with a date type column that has the start date of each fiscal year in it...." --> I have created a table named "Fiscal Lookup" in Smartsheet. The FY column is based upon the formula you recommended so I would remove one of those columns once it is working properly. The Fiscal Year column is using the loooong formula I have been using.

When I created the another table "Fiscal Year Lookup" to do this formula --> =IF(ISDATE([Quarter Start Date]@row), "FY" + RIGHT(IFERROR(MAX(COLLECT({FY Start Date Lookup}, {FY Start Date Lookup}, @cell <= [Quarter Start Date]@row)), ""), 2))

Fiscal Year look up screenshot:

Then I added in the "Fiscal Start" date into my "Fiscal Lookup" sheet my years are now showing last year, it appears I am not understanding this formula setup. I am a bit slow so I apologize in advance.

Paul Newcome

My apologies. Need to add 1 to the year that pulls in from the FY lookup.

=IF(ISDATE([Quarter Start Date]@row), "FY" + RIGHT(IFERROR(MAX(COLLECT({FY Start Date}, {FY Start Date}, @cell<= [Quarter Start Date]@row)) + 1, ""), 2)

Adriane Price

@Paul Newcome - I added in the +1 however still same issue. The starting of 2026 (January 31, 2026) is still displaying FY26 not FY27, but now the formula is creating the fiscal year for this year starting February 4, 2023 to display as FY23 instead of FY24.

Fiscal Start column Formula: =IF(ISDATE([Quarter Start Date]@row), "FY" + RIGHT(IFERROR(MAX(COLLECT({FY Start Date}, {FY Start Date}, @cell<= [Quarter Start Date]@row)) + 1, ""), 2)

I have attempted other variations but it didn't change any outputs. When I did the below formula for FY column (another column I created to see my variations), it looks like it worked. I will plug in the rest of the dates, so far so good!!!

FY column formula used: =IF(ISDATE([Quarter Start Date]@row), "FY" + RIGHT(YEAR([Quarter Start Date]@row) + IF(MONTH([Quarter Start Date]@row) > 1, 1, 1), 2))

Sweet, now I just need to figure out why for 2027 and 2028 the work weeks are off. I have attempted to do a few variations on this formula as well, the closest I was able to get so far has been with this formula for WW column.

WW column formula used: ="WW" + IF(WEEKNUMBER([Quarter Start Date]@row, 2) = 4.5, 53, WEEKNUMBER([Quarter Start Date]@row, 2) - 4)