Excel 2007 : Paid Time Off Automatically?

[GeekyGirl]

So. I'm trying to make a spreadsheet for my payroll person to help her keep track of our employees paid time off hours as they earn them.

How we do it here is as follows:
You work 80 hours, you get 4 hours of PTO. Hours roll over every week until you get 80, then they are reset to 0 and 4 hours are added to your PTO.

How would I go about making a spreadsheet that has our employees in A, their hours worked in B (or over B, C, D, E for each week of the month they work) and their PTO hours in C and have it add the PTO hours up when we type in hours worked and have it add up the PTO and reset the hours worked when it hits 80.

Any help on this would be appreciated.

Thanks for your time.

[Mordred]

Hi GeekyGirl. You'll probably want to customize this a bit but I think the uploaded workbook will do what you want. I don't know how many employees you will enter but you'll have to insert a new row if the count exceeds what I provided. If you do that, remember to copy down the formula in the coloumn for PTO.

Cheers:

[Mordred]

It is slightly revised with the next workbook (it works a little better).

[GeekyGirl]

I like the add employee button. Adding lines isn't difficult for me, but it is a nice convenience touch. The only thing is this: They don't get partial PTO hours. Like, they hit 80 hours they get 4. If they don't have 80 hours, they don't get any PTO.

So. Even though "Michael" is only 1 hour shy of 8 hours of PTO, he only get 4 because he still needs that 1 worked hour. Is there anyway to do that? Like, until 80, it's 0,then after 80 it's 4. 160 is 8. 240 is 12, ect ect?

[Mordred]

Sorry about that GeekyGirl. I'll see if I can fix that for you.

[GeekyGirl]

I appreciate you doing this for me! You're making my job 100% easier.

[Mordred]

I've called in the real pros because my mind is getting into long weekend mode and I am about to leave for home. I will look at this later and if the real gurus on this site haven't helped (which I am certain they will) I will attempt it again later. Good luck.

[GeekyGirl]

Ok, no rush. Just something I'm doing to help streamline PTO for our payroll.

Thanks for your help!

[tigeravatar]

GeekyGirl,

Based on Mordred's updated sample workbook, try the following formula in F2:
=INT(SUM(B2:E2)/80)*4

Then copy down. Does that work for you?

Hope that helps,
~tigeravatar

[Mordred]

Thanks for rescuing me tiger, I am not overly familiar with worksheet functions but I am trying (just not succeeding yet).

[GeekyGirl]

Tiger,
Your solution works perfectly! The only thing I added was a column for TOTAL available PTO. So, if at the end of the month they have PTO left over and I want to start a new month, I can just add the current PTO to the total PTO and have an overall.

Thank you both for all of your help.