Calculate Date 5 years in the future past today's date

Hi, I was just told about this forum. Looks helpful, found a few tips that helped me, except for one answer to my needs. We have an HR application that we need to put in Excel. We have an Hire date and a Modification date. Basically, when someone is hired, the first 5 years they get X amount of hours of vacation. Then years 6-10, they get an extra X amount of hours of vacation, so on, to 16+ years where they max out. I am trying to do a calculation on a date, that needs to be the first day of the following month, unless the date is already the first of the month, and 5 years in the future. Kind of hard to explain, but let me give you examples:

0-5 years they get 192 hours
6-10 years they get 240 hours
11-15 years they get 288 hours
16+ years, they get 336 hours.

The hours are not important for this question, but for the hire date, let's say:

Hired ----------- Next Mod
03/15/2015 ---- 04/01/2020 (it needs to round out to the first of the following month + 5 years)
02/01/2012 ---- 02/01/2017 (Hire date was already on the first of the month, so we don't go to the following month)
07/15/2009 ---- 08/01/2019 (note, this is their second mod date, they are currently in years 6-10, on 8/1/2019 they will change to 11-15 years)
09/05/1995 ---- maxed or 0 (They already have worked 16+ years, so they are maxed out, no future mod date - enter the words "maxed" or a simple zero will do)

I have sort of figured out 5 years in the future by using this formula, =DATE(YEAR(Q2)+5,MONTH(Q2),DAY(Q2)) but not sure how to round to the next month if the day of the month is greater than 1 and have +10 or +15 years if the hire date was further back.

Thanks!

Originally Posted by Tsjallie

Looking at your sample dates this should do it

Formula:

=EOMONTH(EDATE(A2,MIN(3,FLOOR(YEARFRAC(A2,TODAY())/5,1)+1)*5*12),--(DAY(A2)>1)-1)+1

A2 is the startdate. Assumed that the current date is the benchmark.

That's very impressive! That got what I need, I just need to figure out when they are already maxed out. Thank you!

Yes correct, but I need the field to show a zero or the words "maxed" in the cell when they are maxed out.

Originally Posted by daddylonglegs

Isn't maxed out after the 16 year date? Try this version to show the required date or "maxed"

=IF(TODAY()>EOMONTH(Q2-1,192)+1,"maxed",LOOKUP(TODAY(),EOMONTH(Q2-1,{-1,1,2;1,2,3}*60)+1))

Edit: although I'm not sure what you want to do if the date is between 15 and 16 years past - what result should you get if Q2 = 01/01/2000?

Your formula works just like I expected. Thank you! If Q2 = 01/01/2000 then the result would be "maxed". They are 15 years and almost 6 months into it since their hire date, so they max out. Anything greater than 15 years (15 years and 1 day) is maxed. 14 years, 11 months and 29 days, they are still at 10-15 years vacation pay rate.

Originally Posted by daddylonglegs

OK, but you need a small amendment, then

=IF(TODAY()>EOMONTH(Q2-1,180)+1,"maxed",LOOKUP(TODAY(),EOMONTH(Q2-1,{-1,1,2;1,2,3}*60)+1))

I checked a few cells and calculated the dates, looks like this solved it for me. Appreciate it, guys!