Earned & Used Vacation calculations based on Hire Date

Hi, I am calling on you all for help.

The attached workbook has the following information:
vacation hours used on date
calculation for vacation hours used within the 365 days of hire month and day - not calculated
hire date
years and months of service
total vacation earned based on years of service (Data Sheet)
remaining vacation hours - not calculated

The employee's vacation time earned does not roll over and must be used within 365 days of the hire date month/day not year. The employee earns vacation hours based on their anniversary date. I can't figure out how to do a rolling 365 days based on their hire month and day then to calculate their vacation earned and used within that time. I want to be able to tell the employee how much vacation they have remaining.
example: Employee 1 has 48 hours of vacation earned and the employee's anniversary date is 9/1/2011. The employee starts earning vacation after 1 year. The employee wants to know if they used all vacation from 2012-2013 and how much vacation has been earned from 9/1/2014 (current anniversary date). hopefully that makes sense!

If there is an easier way to input the data for vacation used - date and hours, please let me know! this is a rough spreadsheet.

Thank you for all of your help!
K

Vacation Tracker 2014 Forum Question.xlsx

Try the attached.

Hope this helps.

-Z

Yes it did! Thank you! Much more complicated than my skills could take on!

One change I did make, was I added "=" where it was greater than or less than. This corrected vacation being taken right after the anniversary date.

Please Login or Register  to view this content.

Again, thank you very much for this information and the quick response! Much appreciated Z!

K

Always a pleasure to help.

-Z

So it has been awhile since using this and i'm running into issues! now it doesn't calculate properly. The = sign i put in the formula is now saying no vacation was used when clearly it was. i cannot figure this out!!! Please help!! Let me know what you need.