Excel Formula which will add either this year, or next year to a fixed date (e.g. 1st October), dependent upon whether that date has passed this year?

Question

I understand if I want to enter a fixed date and use the current year I can use the following formula:

=DATE(YEAR(TODAY()), 10, 1)

But after the 1st October this year I want the formula to be for next year instead:

=DATE(YEAR(TODAY())+1, 10, 1)

How can I automate this in excel so that I don't have to manually amend this when the 1st October passes? I need to avoid referencing any other date cells in my spreadsheet.

Thanks to anyone that can help.

Answer 1

=IF(MONTH(A1)<10,DATE(YEAR(A1),10,1),DATE(YEAR(A1)+1,10,1))

Answer 2

use a boolean to either add 1 or 0:

=DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()), 10, 1)), 10, 1)