Galtin

New Member
Joined
Feb 12, 2018
Messages
18
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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)
 
Last edited:
Upvote 0
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
 
Upvote 0
You're welcome. Glad you got something that suited your purpose. :)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top