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

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!!!

Hope I did this right Vacation accrual3.xlsx

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?

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.

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.