Calculate future dates at six month intervals

[oesoil]

Hi, I am currently using this date formula =DATE(YEAR(G5),MONTH(G5)+6,DAY(G5)) I type the current date into one cell and it gives me the date 6 month in the future in the adjoining cell.

I now need to change this to go to the 30th of each month except for February which needs to be the 28th

Please could someone help me

Many thanks
Derek

[benishiryo]

welcome to the forum, Derek. what happens if it's a lunar year? does it still end of 28th Feb? if so, try:
=EOMONTH(G5,6)+IF(OR(DAY(EOMONTH(G5,6))={31,29}),-1,0)
or:
=EOMONTH(G5,6)+(OR(DAY(EOMONTH(G5,6))={31,29})*-1)

if not:
=EOMONTH(G5,6)+IF(DAY(EOMONTH(G5,6)=31),-1,0)

[oesoil]

Thank you it worked very well

Derek

[josephteh]

Perhaps, =EDATE(G5,6).