Time Sheet

[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

 

[Pete_UK]

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

 

[Bob Phillips]

=MIN(D2,8)

=MAX(E2-8,0)

--
HTH

Bob Phillips

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

 

[Guest]

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

 

[Pete_UK]

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

 

[Guest]

Hi Pete

I do mean enter the word stat.

Stat in column B=8 hours in column E

 

[Pete_UK]

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

 

[Guest]

Thanks for your help 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

 

[Pete_UK]

Glad to help - thanks for feeding back.

Pete

Thanks for your help Pete

 

[Guest]

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