Simpler Fiscal Year and Quarter Formula

Ashley Ferguson

Hi,

Admittedly, I'm very new when it comes to spreadsheet formulas. I was given this formula for fiscal year and quarter calculations but I was wondering if there was a simpler way to do it. Also, it's in a calendar year format and our fiscal year is July 1 to June 30. I appreciate your help.

=IF(ISDATE([Completion Target]@row), "FY" + IF(MONTH([Completion Target]@row) = 1, RIGHT(YEAR([Completion Target]@row), 2), RIGHT(YEAR([Completion Target]@row) + 1, 2)) + "-Q" + IF(OR(MONTH([Completion Target]@row) = 1, MONTH([Completion Target]@row) >= 11), 4, IF(MONTH([Completion Target]@row) >= 8, 3, IF(MONTH([Completion Target]@row) >= 5, 2, 1))))

Thanks!

Neil Watson

Hi Ashley, we use this for the fiscal year.

="FY" + RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2)

Which shows (for this fiscal year) FY22.

So

=IF(ISDATE([Completion Target]@row), "FY" +RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + "-Q" + IF(OR(MONTH([Completion Target]@row) = 1, MONTH([Completion Target]@row) >= 11), 4, IF(MONTH([Completion Target]@row) >= 8, 3, IF(MONTH([Completion Target]@row) >= 5, 2, 1))))

Ashley Ferguson

Thank you so much. Any thoughts on what I need to adjust so it reflects our quarters properly?

Q1: July/August/September

Q2: October/November/December

Q3: January/February/March

Q4: April/May/June