Skip to main content

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.

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.

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! 😀

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


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!

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! 🙂

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’.


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

Great formula nonetheless. Thank you.

😰and if your FY ends February 28/29th?

Reply


Terms & Conditions