Payroll verification hrs and time off

[Elaine]

I need to create a payroll spreadsheet to calculate time off and verify
accural.
Our employees work 8 hours a day and accrue 1.54 hour sick and 3.08 vacation
each pay period.

If I put in the current totals for sick and vacation how do I calulate to
compute time taken for ea pay period. Oh what would my formula be?

Thank you.
Elaine

 

[Fred Smith]

We need more information. The trite response is to add 1.54 hours to sick leave
every period, and 3.08 hours to vacation. But I expect it's more complicated
than that.

What data are you entering? Hours worked every day? every pay period? What
happens with statutory holidays? How do you know when an employee is taking sick
leave, vacation leave or unpaid leave?

 

[Elaine]

I am entering the current accrual total hours for vacation and sick for each
employee. I attemplted to create a sheet but all my numbers are not
calcuating correctly. My data fields are Name, Accural rate Vac, Accural rate
Sick, Current Vac, Current Sick, Old Vacation, Old SIck, Vac taken, Sick
taken.

All information is entered already. From payroll to payroll I change current
to old and Old to current and then enter current totals. From me entering
the new current sick and vacation that will determine what has been taken.
Statutory holidays and the unpaid are not a problem. I just want to verify
the accural and time off for the pay period.

Thank you very much for quick response.
Elaine

 

[Fred Smith]

It would help if you told us what formulas you entered and how the calculation
was incorrect. Presumably you are doing this manually now, so I don't see that
it's very hard to get the calculations into Excel.

You have columns for Accrual rate Vac & Sick, but in your first post you say
3.08 and 1.54 hours per pay period. Are these accruals constant or variable?

Also, what are you trying to calculate? Vac taken and Sick taken?

The other thing that confuses me is surely you need to enter the hours worked in
a pay period, but maybe that's part of my misunderstanding.

I think the best way to get help is to give an example of a (fictitious)
employee's data, and what results you need.

 

[Elaine]

OK, The accrual rate is constant, the same each pay period. I am trying to
calculate vacation and sick taken. I do not need to enter the hours worked
in a pay period however I do enter accrual (hours) for sick and vacation each
pay period. THis includes adjusted time from last pay period plus accrual.
A B
W.E. W.E. Time Taken/0
Nancy Toi 8/12/07 8/26/07
1) vac 54.26 49.34 formula A1-b1+3.08 = vac taken or 0
2) sick 8.64 10.18 A2-b2+1.54 = sick
taken or 0

Don Smith
3) vac 55.20 58.28
4) sick 34.64 28.18

Tony Jon
5) vac 6.16 9.24
6) sick 30.64 24.18

May Stays
7) vac 55.44 38.52
8) sick 11.72 13.26

Each week I add the new sick and vacation for the week ending. The
calculation is incorrect when actual time is taken. The other formula i
tried is +B1+3.08-A1 But I keep getting neg nums. I am not sure If i should
use "if" expressions.

Thank you very much...
elaine

 

[Fred Smith]

As far as I can tell, your formulas (=A1-B1+3.08 and =A2-B1+1.54) are correct. I
don't see why they don't work.

The results of the formulas for each example given would be:

Nancy Toi, vac, 8
Nancy Toi, Sick, 0
Don Smith, vac, 0
Don Smith, sick, 8
Tony Jon, vac, 0
Tony Jon, sick, 8
May Stays, vac, 20
May Stays, sick, 0

These certainly look reasonable to me. Nancy took one day of vacation, Don and
Tony were sick one day and May took 2 1/2 days vacation.

Why do you think these results are incorrect?

 

[Elaine]

The formula is not correct because when a employee takes vacation or sick
tiime is does not compute. i.e. using the formula =A1-B1+3.08,

Nancy V=44.42 w/e 9/9 V= 49.34 w/e 8/26
Janice S=11.72 w/e 9/9 S=10.18w/e 8/26
Laura V=3.08 w/e 9/9 V= 12.32 w/e 8/26
Janice S=1.54 w/e 9/9 S=6.16 w/e 8/26

Is there a formula to say last week minus this week plus accrual and show in
column what has been taken. Would this be an if expression? Remembering that
the current week could be more than the last week and vice versa therefore
giving a neg number. But I need to show what has been taken each week. Can
you tell me what is the process for this to do each pay period? Because it's
not working for me. I am spending more time than I shoud putting in the data.

Thank you.