Fiscal Year Formula -

I recently had to figure out how to get the fiscal year from a date field. Surprisingly, there wasn’t an explanation and ‘copy and paste’ formula already on here (or I just couldn’t find it).

Here is my take on the challenge:

To use this -

  1. Make sure you have a date field to reference labeled ‘Date’.
  2. Add 2 new fields, one labeled ‘Fiscal Year’, and the other ‘Year’.
  3. Make the one labeled ‘Year’ a formula field and copy and paste the code below into the formula area.
    DATETIME_FORMAT({Date}, ‘YYYY’)
  4. Make the one labeled ‘Fiscal Year’ a formula field and copy and paste the code below into the formula area.
    IF(IS_BEFORE({Date}, DATETIME_PARSE(CONCATENATE(‘06-30’, “-”, Year))), CONCATENATE("FY ", Year), CONCATENATE("FY ", VALUE(Year)+1))
  5. That’s it!

The end of the fiscal year in this formula is June 30th. If you have a different year end, change the ‘06-30’ in the Fiscal Year formula.

I then hide the Year field and group by fiscal years. It is great for seeing historical trends.

1 Like

Perfect! That is very helpful and something I have been struggling to sort. Have tried multiple workarounds to assign 1000s of records to UK financial years (which unhelpfully start 6th April rather than start of month). This did it first time.

@Adrian_Spawforth - Great! I’m happy it worked for you! :grinning:

Thank you for this! I’m using it, but for some reason I’m getting an error. Any reason why that could be?

Screen Shot 2020-08-04 at 6.39.20 PM

Actually, I figured out the problem - there was an extra spaced that was causing the error after the first FY. Thank you for this solution - it saved me so much time!

Great! I’m happy it was helpful! :slight_smile: