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 ?
Solved! Go to Solution.
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.
@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.
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
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?
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
@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’).
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
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!