Calculate remaining vacation days based on anniversary date

[AndreaRice]

Hi there, I'm looking to create a spreadsheet that keeps a running total of vacation days left for each employee. Presently, we have a monthly spreadsheet with the employee name (column A), start date by month & day (Column B) and the remaining columns are days of the month. My manager will be entering the number of hours taken for vacation depending on the date. I'm looking to create a formula which will subtract the used hours from their base vacation hours (i.e. 120hrs for a 15 day vacation period, 80hrs for a 10 day vacation period, etc.). We will have one spreadsheet per month that gets posted for the employees to know their running total. I'm not even sure if this is the best format to do this and we are definitely up for making changes to the format if need be. Does anyone have any suggestions? I don't need a calculation for accrual as it's a set amount depending on years of service but some employees were grandfathered with higher vacation days so a formula for all employees will not work for everyone, I can manually enter their base vacation time and have the formula to subtract but I'll need to make sure the formula includes their anniversary date. Thanks!

 

[portews]

I would create new columns in C for Years of Service; D, Vacation hours granted; E, Hours Used and F, Remaining Hours. From there you can do it all with formulas.

Start by putting these in row 2.
In C put in the formula:

=(YEAR(NOW())-YEAR($B2))

That will subtract the start date in B with today's date and give you years of service.

In D, put in the formula that is your rules for vacation hours by service years, something like:

=IF($C2<5,80,IF($C2<10,120,IF($C2<15,160,200)))

That says, if years less than 5, they get 80 hours, if less than 10, 120 hours, if less than 15, 160 hours, otherwise, 200 hours. You can modify as you need, or continue in the same format. If a particular person has special circumstances, just overwrite the formula with hard numbers.

In E, put in the formula similar to:

=SUM($G2:$AJ2)

That will sum up all the days-of-the-month columns to get the hours used. Substitute your columns as needed.

In F, put in:

=$D2-$E2

to subtract the Hours used from the Hours granted.

Now you can highlight C2:F2, grab it by the lower right corner and drag down to the number of people.

Good luck!

 

[AndreaRice]

Hi Portews, thank you for your help with this. I've done as you showed above and it works wonderfully. My only issue is that we use their anniversary day as the point at which they start anew. This would work perfectly if everyone started their vacation accrual at the beginning of the year but because it depends on their anniversary, it will not provide an accurate depiction of the days left. Do you have any ideas?? Again, thank you so much!!

 

[portews]

I learned something new today! Use DATEDIF to return only the COMPLETED years.
Put this in C

=DATEDIF(B2,NOW(),"Y")

 

[AndreaRice]

Again, thank you Protews for your help! I'm still having some difficulties regarding having the correct amount of hours left for vacation time. For example if "Jason" began working for us on May 8th, his vacation year would be from May 8th to May 7th the following year and would begin a new vacation calendar year. Do you know if there is a formula that would help to show us that he has so many hours of vacation time from the term of May 8th to May 7th? I'm not sure if this explanation makes any sense but again, I really appreciate your help!!

 

[portews]

Did you put the formulas in the colums? Those still should work. What did you get for results?

 

[AndreaRice]

I think I see where I messed up. I'm going to fiddle with this a bit and see where it goes. Again, thank you!!!!