Inserting Quarter and Year for fiscal year (July-June)

jason.scott@sclogistics.com

Tried to use other formulas on here, but can't seem to get it to work. Our fiscal year is June - July. Trying to have a column show Q1 - FY22 if a date referenced is July - Sept 2021, Q2 - FY22 if a date referenced is Oct - Dec 2021, etc. The column I want to read against is "Completed Date" and both columns (Completed Date and Qtr Yr Complete) are formatted for date.

Any help is appreciated.

Genevieve P.

Hi @jason.scott@sclogistics.com

The Qtr Yr Complete column where the formula is input should be a Text/Number column. Then we can use the MONTH and YEAR functions to find out what month the date is in, then return the appropriate quarter.

It will be an IF statement per quarter, so here's one to show you the structure:

Q1 - FY22 = July - Sept 2021

First we check the Year, then we check the MONTH for each option (7, 8, or 9).

=IF(AND(YEAR([Completed Date]@row) = 2021, OR(MONTH([Completed Date]@row) = 7, MONTH([Completed Date]@row) = 8, MONTH([Completed Date]@row) = 9)), "Q1 - FY22")

So now we can add together each of the Quarter options:

=IF(AND(YEAR([Completed Date]@row) = 2021, OR(MONTH([Completed Date]@row) = 7, MONTH([Completed Date]@row) = 8, MONTH([Completed Date]@row) = 9)), "Q1 - FY22", IF(AND(YEAR([Completed Date]@row) = 2021, OR(MONTH([Completed Date]@row) = 10, MONTH([Completed Date]@row) = 11, MONTH([Completed Date]@row) = 12)), "Q2 - FY22", IF(AND(YEAR([Completed Date]@row) = 2022, OR(MONTH([Completed Date]@row) = 1, MONTH([Completed Date]@row) = 2, MONTH([Completed Date]@row) = 3)), "Q3 - FY22", IF(AND(YEAR([Completed Date]@row) = 2022, OR(MONTH([Completed Date]@row) = 4, MONTH([Completed Date]@row) = 5, MONTH([Completed Date]@row) = 6)), "Q4 - FY22", "Not FY22"))))

What do you want it to return if you get into FY23? I've added a quick "Not FY22" if it's an earlier or later date. Let me know if this works for you!

Cheers,

Genevieve