Fiscal Date year changes automatically

[oam]

I need a formula to change fiscal year date to change after 30 June of each year.

Example: 1 July 2021 would change to 1 July 2022 and 30 June 2021 would change to 30 June 2022 and continue to change every year.

I have used to DATE(YEAR(TODAY()),1,1) to change for calendar year but I cannot get it to change for fiscal year.

Any and all help would be appreciated,
Thank you for your time.

[Paul_Hossler]

When does your fiscal year start and end? 30 Jun to 29 Jun?

What happens with 29 June 2021?

[Aussiebear]

When does your fiscal year start and end? 30 Jun to 29 Jun?

If oam's fiscal year is like us layabout convicts, its 1st July to 30th June

[Paul_Hossler]

OP said --


30 June 2021 would change to 30 June 2022

so I'd have guessed that 30 June 2021 would have been the last day of fiscal 2021

and


1 July 2021 would change to 1 July 2022

would be the first day of fiscal 2022

But I agree with you about 1 July to 30 June

[oam]

OP said --



so I'd have guessed that 30 June 2021 would have been the last day of fiscal 2021

and



would be the first day of fiscal 2022

But I agree with you about 1 July to 30 June


The fiscal year starts on 1 July and end on 30 June

[Bob Phillips]

Try

fiscal_year==YEAR(A2)+(--TEXT(A2,"mmdd")>=701)

[Paul_Hossler]

The fiscal year starts on 1 July and end on 30 June


@oam -- I think when you said


30 June 2021 would change to 30 June 2022

is incorrect. 30 June 2021 is still in FY 2021 I believe



I like Bob's formula, but here's an alternative that I changed from what we used for the USGov FY that starts in Oct




=IF(MONTH(A1)>6, YEAR(A1)+1,YEAR(A1))

[Bob Phillips]

I like Bob's formula,

A tad over-engineered with the TEXT function methinks Paul. Better, using your logic and my IF'less formula, is

=YEAR(A2)--(MONTH(A2)>6)

[Paul_Hossler]

Ahhh - an even better approach (but I still think it was clever to use TEXT)

The only advantage for me (personal brain wiring) to my IF formula is that I'm more used to 'If ... Then .. Else ..' thinking even when it's less efficient

[oam]

Want to thank all who responded, you made me think in a different direction and make it work.

Again, thank all of you for your help.