Formula To Reset a Count Based On Cell Value

I've found a # of threads on this subject but have been unable to find the function I need.

I've got a spreadsheet (attached) to calculate vacation time, but I can't figure out how to make the used time reset on an anniversay date. For example, if Employee 1 has earned 80 hours of vacation but only used 40 by his 4 year anniversary date, how do I make unused time roll back to 80 the day of his anniversary?

Here's the structure: 2 Personal days given Jan. 1, then 2 more on July 1 to employees here at least 6 months. Those whose 6 months fall after or between are awarded 2 days on their anniversary then accrue as normal. Unused personal days are lost at the end of the year. Vacation time is 40 hours after 1 year, 80 hours at 3 yrs, then 120 hours at 5+. These are given strictly by anniversary date & are forfeited if unused by the next anniversary.

Thanks for your help!

Can you post as an xls file?

Okay, correct me if I'm wrong. What you need is code that will clear the used personal days of all employees on Jan 1st and which will clear used vacation days on their anniversary date? If it's not a year in which their anniversary date has a significance (e.g. 2 years), does vacation still run from Anniversary date to Anniversary date or from Jan 1 to Jan 1 (use it or lose it)?

Originally Posted by ChemistB

Okay, correct me if I'm wrong. What you need is code that will clear the used personal days of all employees on Jan 1st and which will clear used vacation days on their anniversary date? If it's not a year in which their anniversary date has a significance (e.g. 2 years), does vacation still run from Anniversary date to Anniversary date or from Jan 1 to Jan 1 (use it or lose it)?

Personal days are annual, but for now I've got the personal dates taken care of, since they reset for everybody first of the year they can just be manually deleted.

Vacation time is strictly set by anniversary as far as when it is earned & when it expires. So, even if it's an insignificant anniversary, it'll still reset on that date. If they've used 30 of their 40 hours when they hit their 2 year, it needs to reset from 30 used to 0 used, as they'll have lost what they had remaining, but will at the same time be receiving their next block of vacation time. I've got the time earned portion set to automatically adjust on the anniversary if the employee reaches the next milestone (otherwise there's no need for it to change), but I now need to get the time used to reset to zero every anniversary.

Hopefully that cleared things up instead of making more confusion - took me a while to make sense of how it's being done myself!

This definitely requires VBA so I would post in the Programming thread with a link to this thread. I would think code in the workbook module upon opening that checks aniversary dates and clears the appropriate rows in your vacation sheet.