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:
- Open the Query Editor
- Select the query that you want to add the financial year column to
- Add a new custom column
- 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 🙂
