Using EDATE

[Galtin]

Hello.

I need to calculate what the next anniversary date is if today's date is more than 365 days than the hire date:

Creating a budget for 2019. The information below is regarding an employee's hire date which will affect a merit increase.

Current formula:
A1 = reflects the Hire Date the employee started. Hire Date is 04/01/2017
B2 = Reflects the Anniversary Date as in: EDATE(A1,12). Anniversary Date will be 04/01/2018.
If I enter a Hire Date that is less than 1 year old the EDATE formula works perfectly.

I need B2 to reflect the Anniversary Date of 04/01/2019 and so on for my example above. The Hire Date is always.

Any help with this is much appreciated.

Thank you,

Galtin

 

[wwbwb]

Try this:

=EDATE(A1,12*(YEAR(TODAY())-YEAR(A1)+1))

 

[Galtin]

wwbwb,

Thank you for taking the time to respond. I am not at my computer right now but I will try it first thing in the morning and let you know if any issues arise by me not entering it correctly.

Galtin

 

[Peter_SSs]

Try this:

=EDATE(A1,12*(YEAR(TODAY())-YEAR(A1)+1))

By my understanding, that will only work some of the time. For example, if A1 contains 15 November 2015 your formula returns 15 November 2019, which is not "the next anniversary date" as requested by the OP.

My suggestion is.
=EDATE(A1,(DATEDIF(A1,TODAY(),"y")+1)*12)

 

[Galtin]

Thank you, both!

I tried both ways and they both work but after trying a number of things I think Peter_SSs provides what I need.

Again, thank you - you have saved me a lot of frustration.

Galtin

 

[Peter_SSs]

You're welcome. Glad you got something that suited your purpose.