Calculating Fiscal/Financial year (Australi)

[StephenT]

Hi all

I know this question has been asked many times, so I apologise in advance, and hope it's a quick and easy answer 😊

The Australian Fiscal/Financial year is from 1st July to 30th June.

Can someone tell me how to create a formula to display the correct FY, based on a date format of YYYY-MM-DD ?

[FrederikE]

Hey @StephenT,

You could use a formula like the following:

Just compares if the month is July or later, then the year is taken as the FY, otherwise the prev. year is taken as the FY.

[DataNath]

@StephenT would something like this work? I already had this calculation made for another project of mine - just adapted it to suit the Australian dates! Let me know if it it needs any amendment. Obviously for your use case you'd replace all of the instances of [%Dummy Date field%] with your own target date field.

[StephenT]

Hi @DataNath 

Thanks for the formula.

I've added the formula to my workflow however i have just noticed that some dates are appearing in the wrong fiscal year, e.g. some date of 2022-07-02 are showing as in both  FY 2021/22 and FY 2022/23.

I did have to make 1 small modification (modification below), as based on your formula the date of 2022-06-30 was showing on the FY 2022/23, instead of the correct FY of 2021/22.

After this modification i now have 2022-07-01 showing in FY 2021/22 as well as FY 2022/23, so not sure what I have done wrong

[DataNath]

Hey @StephenT, apologies, I had the complete wrong month in mind when adjusting my formula! If you change the bit you’ve circled to ‘-06-30’, does that remedy the issue?

[StephenT]

I reverted back to -06-30 and It's now showing 1 instance of 2022-06-30 in the FY of 2022/23, not 2021/22

[DataNath · Accepted solution]

@StephenT apologies but I’m no longer near my laptop and heading away now. Did some extensive testing for my project and always seemed consistent. Is your incoming field definitely a Date rather than perhaps DateTime or something? Only other thing I can think of trying (unless I’m missing something obvious) is to wrap the incoming date or the other side of the > in the ToDate() function I.e. ToDate(ToString….’-06-30’).

[StephenT]

Hi @DataNath 
I re-checked my workflow and noticed the date field was actually set yo date/time, instead of date.
I have changed it to date, and it works perfectly.
I will like your reply, and accept it as a solution
Thanks for your help

[maredibelgrado]

Hey @DataNath and @StephenT,

Thank you both for discussing this question in the community. I read many pages within the community and I could not seem to find an answer. This was exactly what I was looking for.

Much appreciated!