Time Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to make a time sheet that automatically calculates regular time
and overtime.

B C D E F

Time Worked Total Reg O/T
Start End Hours Hours Hours


In column E, I would like it to add up the total amount of regular hours
worked (up to 8 hours) on a given day. In column F I would like a total of
any hours over 8 hours in a given day.

Can someone help me with the formula for this.

Thanks
 
I assume that you will enter the start time and Worked End in Excel
time format, i.e. something like 8:00 and 17:00 respectively. If so,
then you will probably want to work in hours for the rest of the sheet,
so enter these formulae in the cells shown:

D2: =(C2-B2)*24
E2: =MIN(D2,8)
F2: =IF(D2>8,D2-8,0)

Format these three cells as number with 2 decimal places, then copy the
formulae down as required.

Hope this helps.

Pete
 
=MIN(D2,8)

=MAX(E2-8,0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Thanks Pete,

It works fine now. One more question if I enter stat in column B I would
like it if it automatically puts 8 hours in column E.

Thanks

Kevin
 
Sorry, I don't understand. Do you mean you want to enter the word
"stat" in column B, or is this a typo for "start" meaning you only want
to put the start time in and if end time is missing then you want to
default to 8 hours work?

Please elaborate.

Pete
 
Hi Kevin,

The following change to the formula in D2 should achieve what you want
to do:

D2: =IF(B2="stat",8,(C2-B2)*24)

The other formula remain the same, i.e.:

E2: =MIN(D2,8)
F2: =IF(D2>8,D2-8,0)

Hope this is what you want.

Pete
 
Thanks for your help Pete

Pete_UK said:
Hi Kevin,

The following change to the formula in D2 should achieve what you want
to do:

D2: =IF(B2="stat",8,(C2-B2)*24)

The other formula remain the same, i.e.:

E2: =MIN(D2,8)
F2: =IF(D2>8,D2-8,0)

Hope this is what you want.

Pete
Click to expand...
 
B C D E F
Time Worked Total Reg O/T
Start End Hour Hours Hours
8:00 17:00 9 8 1

D3 =(C3-B3)*24 Total hours
E3 =MIN(MAX(D3,0),8) Reg hours
F3 =MAX(D3-8,0) O/T hours
 
Back
Top