Quarter and Fiscal Year formula based on timelines

Paula ZM

Hi!

Our quarter starts on feb. and sometimes we have events starting on jan (Q4 - FY2022) and ending on mar (Q1 - FY 2023). I already added the formula for Quarter and Fiscal Year based on the start date, but I would like to consider both start and end date to have the correct quarter and fiscal year timeline.

Here, for example, the start date is on january Q4 FY2022, but the end date is on february Q1 FY2023. How could I add both quarters and fiscal years? Like this: Q4 - Q1 and FY2022 - FY2023

Now, I am using this formulas:

Quarter:

=IFERROR(IF(MONTH([Start Date]@row) = 1, "Q4", IF(MONTH([Start Date]@row) = 2, "Q1", IF(MONTH([Start Date]@row) = 3, "Q1", IF(MONTH([Start Date]@row) = 4, "Q1", IF(MONTH([Start Date]@row) = 5, "Q2", IF(MONTH([Start Date]@row) = 6, "Q2", IF(MONTH([Start Date]@row) = 7, "Q2", IF(MONTH([Start Date]@row) = 8, "Q3", IF(MONTH([Start Date]@row) = 9, "Q3", IF(MONTH([Start Date]@row) = 10, "Q3", IF(MONTH([Start Date]@row) = 11, "Q4", IF(MONTH([Start Date]@row) = 12, "Q4")))))))))))), "")

Fiscal Year:

=IFERROR(IF(MONTH([Start Date]@row) < 2, "FY" + [Calendar Year]@row, "FY" + ([Calendar Year]@row + 1)), "")

Any suggestions?

Thanks in advance!

Paul Newcome

We are going to end up having to repeat the formulas 4 times each (essentially), so my first suggestion is that we shorten the quarter formula as much as possible. The year formula could be approached differently, but it wouldn't really be much "shorter" so we will leave that one as is.

=IFERROR(IF(OR(MONTH([Start Date]@row)>= 11, MONTH([Start Date]@row) = 1), "Q4", IF(MONTH([Start Date]@row)>= 8, "Q3", IF(MONTH([Start Date]@row)>= 5, "Q2", "Q1"))), "")

Now that we have that shortened up a bit, we can work on the overall formula. Here is the basic idea...

Output the start date quarter + IF start date quarter does not equal end date quarter, output " - end date quarter".

That's going to look like this:

=IFERROR(IF(OR(MONTH([Start Date]@row)>= 11, MONTH([Start Date]@row) = 1, "Q4", IF(MONTH([Start Date]@row)>= 8, "Q3", IF(MONTH([Start Date]@row)>= 5, "Q2", "Q1"))), "") + IF(IFERROR(IF(OR(MONTH([Start Date]@row)>= 11, MONTH([Start Date]@row) = 1, "Q4", IF(MONTH([Start Date]@row)>= 8, "Q3", IF(MONTH([Start Date]@row)>= 5, "Q2", "Q1"))), "") <> IFERROR(IF(OR(MONTH([End Date]@row)>= 11, MONTH([End Date]@row) = 1, "Q4", IF(MONTH([End Date]@row)>= 8, "Q3", IF(MONTH([End Date]@row)>= 5, "Q2", "Q1"))), ""), " - " + IFERROR(IF(OR(MONTH([End Date]@row)>= 11, MONTH([End Date]@row) = 1, "Q4", IF(MONTH([End Date]@row)>= 8, "Q3", IF(MONTH([End Date]@row)>= 5, "Q2", "Q1"))), ""))

Now we use the same logic for the FY:

=IFERROR(IF(MONTH([Start Date]@row) < 2, "FY" + [Calendar Year]@row, "FY" + ([Calendar Year]@row + 1)), "") + IF(IFERROR(IF(MONTH([Start Date]@row) < 2, "FY" + [Calendar Year]@row, "FY" + ([Calendar Year]@row + 1)), "") <> IFERROR(IF(MONTH([End Date]@row) < 2, "FY" + [Calendar Year]@row, "FY" + ([Calendar Year]@row + 1)), ""), " - " + IFERROR(IF(MONTH([End Date]@row) < 2, "FY" + [Calendar Year]@row, "FY" + ([Calendar Year]@row + 1)), ""))