Next 5 April from any given date in Excel

[shazzaxyz]

Hi All
I'm trying to return the next 05 April from any given date, with the cell A11 holding the date. I was using this:
=IF(MONTH(A11+1)=4,A11+1,DATE(YEAR(A11)+1,4,5))
Which works fine unless the date I'm using is a month less than 4 (eg jan,feb,march,April) as it will then return to me the 5 April after the one i need, 2021 instead of 2020.

So i then wrote this one, which gives me an error:
=IF(MONTH(A11)<4,(YEAR(A11),month(4),day(05)),(YEAR(A11)+1,month(04),day(05)))

 

[Fluff]

Hi & welcome to MrExcel.
Maybe

=IF(MONTH(A2)<4,DATE(YEAR(A2),4,5),DATE(YEAR(A2)+1,4,5))

 

[Rick Rothstein]

@shazzaxyz... Give this formula a try,

=DATE(YEAR(A2)+(TEXT(A2,"yyyymmdd")>YEAR(A2)&"0404"),4,5)

@Fluff...check your formula for April 1st through 4th.

 

[Fluff]

I wouldn't be that surprised if it's wrong as I'm not really sure what the OP is after, I just corrected the OP's 2nd formula.