# Date of annual increment

#### baidya91

##### Board Regular
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.

Last edited:

#### etaf

##### Well-known Member
=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

Last edited:

Thanks

#### etaf

##### Well-known Member
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))))<>

Last edited:

#### baidya91

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

#### etaf

##### Well-known Member
=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
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]``

Last edited: