Fiscal Year Start Date

[Leanne82]

Hi
I am looking for a formula to show the first day of a fiscal year which starts on 01/09 each year. I have column A with a list of various dates i.e. 11/10/2020 and I want column B to show the first day of the fiscal year i.e 01/09/2020. I also need the result to be in a date format dd/mm/yyyy
I have tried DATE(YEAR(a5)+(MONTH(a5)>9),9,1) but this seems to show the end date

 

[James006]

Hi,

To convert the current month to your fiscal month, you can test :
= MOD(current_month+3,12)+1

 

[Phuoc]

Try this

=DATE(YEAR(A5)-(MONTH(A5)<9),9,1)

 

[Leanne82]

Try this

=DATE(YEAR(A5)-(MONTH(A5)<9),9,1)

This works perfectly, thank you

 

[AlanY]

try this

=DATE(YEAR(A5)+INT((A5-DATE(YEAR(A5),9,1))/365.25),9,1)

 

[hrayani]

Hello,

Try this

=DATE(TEXT(A1,"yyy"),9,1)

 

[Phuoc]

A little shorter:

=DATE(YEAR(A5+122)-1,9,1)