May 04, 2020 12:27 PM
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 -
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.
May 11, 2020 01:21 AM
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.
May 11, 2020 07:58 AM
Aug 04, 2020 06:40 PM
Thank you for this! I’m using it, but for some reason I’m getting an error. Any reason why that could be?
Aug 04, 2020 06:47 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!
Aug 05, 2020 09:01 AM
Great! I’m happy it was helpful! :slightly_smiling_face:
Jan 10, 2021 10:30 PM
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’.
Dec 03, 2022 05:56 PM
I think one other issues was due to ASCII / Windows type of quotes instead of the standard UTF8 quotes .
Great formula nonetheless. Thank you.