Formula to change value on certain date

[TheTubaGeek]

I am trying to create a vacation tracker for work. I have the following fields:

Hire Date (MM/DD/YYYY)
Total Time with Company in years (=(TODAY()-<Hire Date Field>)/365)
Vacation Hours Allotted
Vacation Hours Remaining (Calculated from the Allotted Field and a sum of all of the time sheets within the workbook)

I would like to have the Vacation Hours Alotted value dynamically reset to the appropriate value on the anniversary of the hire date posted, so employees can easily track "at a glance" how much time they have left before their vacation time resets. For example, if I was hired on February 1, I would like to be able to look at my vacation hours remaining field on January 1 and know how much time I have left so I can use it before I lose it.

Is there a way to use an IF Statement to do this? I already have an IF Statement in there that sets the hours on specific anniversary dates, but not for every year:

=IF(B2="","",INDEX({40,40,80,120,160}, MATCH(B2,{0,1,2,5,10})))

I would rather not have to set a massive IF statement to get my desired result, and I am not 100% comfortable with using a macro or VBA to achieve the desired result.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[TheTubaGeek]

File attached

[boopathiraja]

you may try this

for month

=DATEDIF(B1,TODAY(),"ym")

for date

=DATEDIF(B1,TODAY(),"md")

or if you need number of days

=DATEDIF(B1,TODAY(),"ym")*30+DATEDIF(B1,TODAY(),"md")

[TheTubaGeek]

OK, I see where that would calculate the difference and would essentially allow the change to occur when the month/date difference is 0. Now, I need to have it do that AND increase at specific milestones according to the value in cell B2.

[TheTubaGeek]

OK, I guess my statement was a little confusing ...

It already updates on milestone dates, but I want it to update every year to the appropriate value and then increase at the specific milestones.

[TheTubaGeek]

Does anyone else have some input, or would what boopathiraja posted work for what I want to do? If I have to use a macro, I will, but I'd rather not if I can avoid it.

[TheTubaGeek]

OK, after looking around, I found something to try, but when I enter the formula, I get an error of too many arguments (carriage returns entered to help with following the logic):

=IF
(TODAY()>=DATE(YEAR(B1)+10,MONTH(b1),DAY(b1)),160,
TODAY()>=DATE(YEAR(B1)+5,MONTH(b1),DAY(b1)),120,
TODAY()>=DATE(YEAR(B1)+2,MONTH(B1),DAY(B1)),80,
TODAY()>=DATE(YEAR(B1)+1,MONTH(B1),DAY(B1)),40)))

I try to get some help and the result comes up as "Volatile" when I look at the function arguments.

EDIT: I figured out what I did wrong and was able to fix it.