MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Chris on December 18, 2001 5:09 PM

I am trying to automate our hospitals timesheet.
I have the simple stuff done. Hrs worked, OT, Subracting vacation hours used.
I have a running total on vacation hours
Ex: I have a total of 10 hrs vacation time. I get paid twice a month and get 5 hrs on each payperiod.
So I used 8 hrs in the first payperiod.
I know how I would 10-8 but how do I have the sheet add 5 hrs at the end of each payperiod?

And how do I have the sheep NOT add 5 hrs if the person had "Leave Without Pay"

Thanks for your help

Posted by Jacob on December 18, 2001 6:00 PM


Maybe this will get you started.

You get 5 hours per pay period and there are 26 pay periods a year so you get (5*26)/365 hours per day or .356164

now you can have a cell say A1 that says = ((today()-B1-C1)*(.356164))-D1

Where B1 has first day of employment
C1 has unpaid leave total days
D1 has vacation time already used (in hours)

Hope this helps