
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 -
- Make sure you have a date field to reference labeled ‘Date’.
- Add 2 new fields, one labeled ‘Fiscal Year’, and the other ‘Year’.
- Make the one labeled ‘Year’ a formula field and copy and paste the code below into the formula area.
DATETIME_FORMAT({Date}, ‘YYYY’) - 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)) - 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 11, 2020 07:58 AM
@Adrian_Spawforth - Great! I’m happy it worked for you! :grinning:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 05, 2020 09:01 AM
Great! I’m happy it was helpful! :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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’.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 27, 2024 01:35 AM
😰and if your FY ends February 28/29th?
