Current financial year and Pior financial year

[Sarah7828]

Hi

I am trying to create current and prior financial year formula based on end of month date

End of month Date : eg 30/9/2024 - Cell B1
Current Financial year : e.g 30/6/2025 - Cell B2
Prior Financial year : e.g 30/6/2024 - Cell B3

IF(AND(EOMONTH(B1,0)>=DATE(YEAR(B1),7,1),(EOMONTH(B1,0)<=DATE(YEAR(B1)+1,6,30))),DATE((YEAR(B1)+1),6,30,DATE((YEAR(B1)-1),6,30))

not sure how to fix it

Thank you

 

[rowebca]

You should replace your formula with this:

IF(AND(EOMONTH(B1,0)>=DATE(YEAR(B1),7,1),(EOMONTH(B1,0)<=DATE(YEAR(B1)+1,6,30))),DATE((YEAR(B1)+1),6,30),DATE((YEAR(B1)-1),6,30))

Regards,
GB

 

[djclements]

The generic syntax to return the current fiscal year end date based on the current month end date is:

=DATE(YEAR(CurrentMonthEnd)+(MONTH(CurrentMonthEnd)>YearEndMonth),YearEndMonth+1,0)

So, with the current month end date in cell B1 and a fiscal year ending in June (6), the formula in cell B2 would look like this:

=DATE(YEAR(B1)+(MONTH(B1)>6),6+1,0)

With the DATE function, day 0 is interpreted as the last day of the previous month, so July 0 will return June 30. (Note: you could also just input 7 for the month argument instead of 6+1.)

The prior year end date formula in cell B3 would then be:

=EOMONTH(B2,-12)

 

[Sarah7828]

Thank you. My IF don’t make sense . Appreciate it