Fiscal Year Calculation

Judy S

I need a column to display the Fiscal Year based off Completed Date.

Here is my Fiscal Quarter formula and a screenshot. Can anyone offer assistance with a formula to auto calculate the Fiscal Year in a two digit format:

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

Ipshita

Hi @Judy S

A simple way to get your fiscal year would be just by using the formula for the year column =YEAR([Completed Date]@row)

Also, use the following formula to calculate the Quarter - (where End Date is your Completed Date)

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

Lastly, use the JOIN formula to get the Fiscal Year and Fiscal Quarter for the last column. Additionally, you can wrap up each of your formula with the IFERROR statement.

Hope this helps :)

Cheers!

Ipshita