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.

=SUMIFS(B10:B500,$A$10:$A$500,"<="&DATE(IF(MONTH(B5)<=MONTH(TODAY()),YEAR(TODAY())+1,YEAR(TODAY())),MONTH(B5),DAY(B5)),$A$10:$A$500,">="&DATE(IF(MONTH(B5)<=MONTH(TODAY()),YEAR(TODAY()),YEAR(TODAY())-1),MONTH(B5),DAY(B5)))

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.