Annual Dates

[MFish]

Hello,

My goal is to reflect the annual dates of someones employment with a formula. I have over 2k employees with random dates ranging back from 1995 to today. I need to place a 6 mo. "Probationary" date which I figured out by doing, =edate(A1, 6) and it reflected the 6 months from that date and so on and so forth. However, I know you may be saying, why not just do =edate(A1, 12)? I need the updated year that will be coming up.

Example:

I have a gentleman with a hire date of 1/24/1999. Their 6 mo. probation date was 7/24/1999 but I need their annual date to be 1/24/2021 (Because we can't use this years date due to it already passing).

But if someone has a date of 9/10/2010, as an example, it'll be 9/10/2020 (As the date hasn't reached yet this year).

Hope this is making sense and I hope it's something really easy.

Thank you.

 

[mainfolder]

Assume that the first employment date is on cell A1. Then to calculate annual date; you can use the below formula and copy 2k cells below,,,

=IF(TODAY()>DATE(YEAR(TODAY());MONTH(A1);DAY(A1));DATE(YEAR(TODAY())+1;MONTH(A1);DAY(A1));DATE(YEAR(TODAY());MONTH(A1);DAY(A1)))

* will not work if someone's employment date is 29 February

 

[MFish]

Awesome, works great. This is what I needed.