Date of annual increment

baidya91

Board Regular
Joined
Jun 1, 2016
Messages
128
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
Joined
Oct 24, 2012
Messages
3,392
=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:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,392
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:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,392
=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:

Forum statistics

Threads
1,082,510
Messages
5,365,993
Members
400,865
Latest member
LuciaRomo

Some videos you may like

This Week's Hot Topics

Top