Financial Year via nested if formula

[CYParker]

Just wanted to share this, as I was finding a lot of what appeared to be very complex (to me at least) solutions for something pretty simple - displaying the financial year, in a format of choice, based on a date.

Steps:

1. Open the Query Editor
2. Select the query that you want to add the financial year column to
3. Add a new custom column
4. Use the below nested if statement as the Custom column formula
   • Adjust the #date(yyyy,m,d) values and financial year string "20yy-yy" as required.
   • Replace [doc_startdate] with the name of your date column (past the code into Word and do a find and replace)

if [doc_startdate] = null then "" else if [doc_startdate] < #date(2020,7,1) then "2019-20" else if [doc_startdate] < #date(2021,7,1) then "2020-21" else if [doc_startdate] < #date(2022,7,1) then "2021-22" else if [doc_startdate] < #date(2023,7,1) then "2022-23" else if [doc_startdate] < #date(2024,7,1) then "2023-24" else "ERROR"

Done!

Hope that it helps someone 🙂