Help

Fiscal Year Formula -

Topic Labels: Formulas
3538 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Nathan_Feemster
6 - Interface Innovator
6 - Interface Innovator

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:
Screen Shot 2020-05-04 at 9.39.41 AM

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.

7 Replies 7
Adrian_Spawfort
4 - Data Explorer
4 - Data Explorer

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:

Marketing_Team
4 - Data Explorer
4 - Data Explorer

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

Marketing_Team
4 - Data Explorer
4 - Data Explorer

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! :slightly_smiling_face:

Jackson_Scott
5 - Automation Enthusiast
5 - Automation Enthusiast

Nice one Nathan. You can also use the following formula in a single field if you’re happy with just returning the digits of the fiscal year (without the ‘FY’).

IF((DATETIME_FORMAT(Date, 'MM'))>6,((DATETIME_FORMAT(Date, 'YY'))+1), ((DATETIME_FORMAT(Date, 'YY'))))

*or replace both 'YY’s with ‘YYYY’ if you want ‘2019’ rather than just ‘19’.

Screen Shot 2021-01-11 at 4.27.14 pm

michaelhabib
4 - Data Explorer
4 - Data Explorer

I think one other issues was due to ASCII / Windows type of quotes instead of the standard UTF8 quotes .

Great formula nonetheless. Thank you.