Date of annual increment

[baidya91]

Is there any excel formula to find out annual increment date?
Example: If A1= 01/05/2017 as appointment date, B1= 31/04/2018 as annual increment date, If A1=25/07/2017, B2=30/06/2018....etc.

 

[etaf]

=EOMONTH(A1,11)

But that will only give you 1 year after the appointment
were you after this for any year
so if appointment date 23/1/1998 - you would want 31/12/19 now as we have passed 31/12/18

 

[baidya91]

Thanks

 

[etaf]

this may work for any year and the spreadsheet opening

=IF(DATE(YEAR(TODAY()),MONTH(EOMONTH(G9,11)),DAY(EOMONTH(G9,11))) < TODAY(),DATE(YEAR(TODAY())+1,MONTH(EOMONTH(G9,11)),DAY(EOMONTH(G9,11))),DATE(YEAR(TODAY()),MONTH(EOMONTH(G9,11)),DAY(EOMONTH(G9,11))))<today(),date(year(today())+1,month(eomonth(g9,11)),day(eomonth(g9,11))),date(year(today()),month(eomonth(g9,11)),day(eomonth(g9,11))))< html=""></today(),date(year(today())+1,month(eomonth(g9,11)),day(eomonth(g9,11))),date(year(today()),month(eomonth(g9,11)),day(eomonth(g9,11))))<>

 

[baidya91]

Second formula does not work. It gives the result #name ?

 

[etaf]

=IF(DATE(YEAR(TODAY()),MONTH(EOMONTH(a1,11)),DAY(EOMONTH(a1,11))) < TODAY(),DATE(YEAR(TODAY())+1,MONTH(EOMONTH(a1,11)),DAY(EOMONTH(a1,11))),DATE(YEAR(TODAY()),MONTH(EOMONTH(a1,11)),DAY(EOMONTH(a1,11))))

its adding a space in EOMONTH

editing and its OK

try with code

[COLOR=#333333][FONT=Verdana]=IF(DATE(YEAR(TODAY()),MONTH(EOMONTH(a1,11)),DAY(EOMONTH(a1,11))) < TODAY(),DATE(YEAR(TODAY())+1,MONTH(EOMONTH(a1,11)),DAY(EOMONTH(a1,11))),DATE(YEAR(TODAY()),MONTH(EOMONTH(a1,11)),DAY(EOM ONTH(a1,11))))[/FONT][/COLOR]