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

Samantha Baruah

What if your FY spans two years? So my FY starts in April and ends the following March.

KPH

How to Calculate Fiscal Year from a date

If you have the date in a date formatted column, you can use an IF statement, extract the month from the date, and use that to return either the current year or the year plus 1.

Assuming April 1 2024-Mar 31 2025 is financial year 2025 then if the month is between 1 and 3 the fiscal year is the current year. But if the month is 4 to 12, the fiscal year is the current year plus 1.

Your IF formula would look like this:

=IF(MONTH(Date@row) < 4, YEAR(Date@row), YEAR(Date@row) + 1)

Which means if the month in the date in the Date column is less than 4 return the year from the date in the Date column. If not return the year from the date in the Date column plus 1.

Here it is:

Does that help?