Need a formula that calculates vacation hours accrued and used based on hours worked

[excelnewbie101]

Good afternoon. I am new with Excel and I desperately need some help. I was hoping someone could help me.

I need to keep track of employee vacations and I am very new to Excel.

I need a formula to calculate that beginning with their start date, for every 40 hours worked they earn a certain amount of vacation days, and then I need to subtract vacation days already used.

for example: Joe who is salaried, started July 14, 2014 he gets 15 days a year but they are built up during the year. He works a 40 hours per week. So he basically earns 10 hours a month or 1.25 days a month and he has used 4 days already.
But Susie who is hourly with a start date of January 5, 2015, gets 8 hours for every 200 hours worked and the number of hours she works varies from week to week.

Is there any chance someone can help me??

[skywriter]

Hi excelnewbie101,

The formulas are easily within the capabilities of the gurus on this board, but I think you have to post a sheet with your data first. It's important to see how it's laid out, at least to me, maybe someone else can give you a formula off the top of their head, but I can't.

If you have an example spreadsheet, hit the go advanced tab at the bottom of the box when you are replying, click on the paperclip and then you can attach a worksheet.
Just remove any sensitive names, employee numbers etc.

Good Luck!!!

[excelnewbie101]

Hope I did this right Vacation accrual3.xlsx

[skywriter]

for example: Joe who is salaried, started July 14, 2014 he gets 15 days a year but they are built up during the year. He works a 40 hours per week. So he basically earns 10 hours a month or 1.25 days a month and he has used 4 days already.
But Susie who is hourly with a start date of January 5, 2015, gets 8 hours for every 200 hours worked and the number of hours she works varies from week to week.

Questions:
I see you have a formula G11 for Susie. You state above she gets 8 hours for every 200 worked which is very simple just divide the hours worked by 200 and multiply by 8, which works out to 5.92 hours. Is this what you want because the complicated formula you have says 8 hours earned?
How do you want Joe's vacation hours accrued. Do you want it to accrue based on weeks worked or once a month or what and exactly how many hours for whatever time period you decide?

[excelnewbie101]

I'm not sure exactly how to ask this.....Susie gets 8 hours vacation pay for every 200 hours she works. All employees get paid once monthly. What I need to be able to do is, every month, go in and add their hours into the spread sheet and have it calculate the vacation time they earned (in either days or hours)....I then need to enter any used vacation days during that month and have that subtracted from their accrued time. I'm sorry if I'm wording this wrong, I am really new to this. I thank you for any help you can give

[skywriter]

Let's start simple and we'll get you to where you need to be. So the sheet now is very basic, the only cells I touched are highlighted in yellow. Change the hours worked the vacation earned changes. Subtract some hours and the remaining vacation changes. Now let me know what needs to be changed, added, deleted, etc.

[excelnewbie101]

Ok, that helps a lot, thank you! But lets, say I want it to show days earned instead of hours. So for every 200 hours worked, she earns one day and then subtract days used. Now the guys are all salaried. They each have a different vacation accrual set up that is built up starting with their anniversary date. So Joe, who started on June 16th, 2014 gets 4 weeks every year and he has used 10 days, Brad who started on July 14th, 2014 gets 3 weeks and has used 3 days and Mike who started on November 10th, 2014 also gets 3 weeks. and he has used 1 day. Thank you ever so much for your help!

[skywriter]

I asked you in post#4 about Joe the salaried person and you didn't directly answer the question, so I went with hours. You said he earns 10 hours a month, so if you want to convert hours to days just use the formula Hours/8. I was an operations manager myself and we always accrued vacation and showed used vacation in hours. I don't know why you would want to accrue then as days and use them as days. We would let people take vacation hours that were not necessarily a full day. If someone left sick 2 hours early didn't have sick time and wanted to use 2 vacation hours we let them. Using 0.25 days just seems confusing.