Time Sheet
 

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

Time Sheet
 

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(D28,D2-8,0)

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

Hope this helps.

Pete

Kevin wrote:
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

Time Sheet
 

=MIN(D2,8)

=MAX(E2-8,0)

--
HTH

Bob Phillips

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

"Kevin" wrote in message
...
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

Time Sheet
 

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" wrote:

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(D28,D2-8,0)

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

Hope this helps.

Pete

Kevin wrote:
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

Time Sheet
 

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

Kevin wrote:
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" wrote:

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(D28,D2-8,0)

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

Hope this helps.

Pete

Kevin wrote:
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

Time Sheet
 

Hi Pete

I do mean enter the word stat.

Stat in column B=8 hours in column E



"Pete_UK" wrote:

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

Kevin wrote:
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" wrote:

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(D28,D2-8,0)

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

Hope this helps.

Pete

Kevin wrote:
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

Time Sheet
 

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(D28,D2-8,0)

Hope this is what you want.

Pete

Kevin wrote:
Hi Pete

I do mean enter the word stat.

Stat in column B=8 hours in column E



"Pete_UK" wrote:

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

Kevin wrote:
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" wrote:

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(D28,D2-8,0)

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

Hope this helps.

Pete

Kevin wrote:
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

Time Sheet
 

Thanks for your help Pete

"Pete_UK" wrote:

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(D28,D2-8,0)

Hope this is what you want.

Pete

Kevin wrote:
Hi Pete

I do mean enter the word stat.

Stat in column B=8 hours in column E



"Pete_UK" wrote:

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

Kevin wrote:
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" wrote:

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(D28,D2-8,0)

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

Hope this helps.

Pete

Kevin wrote:
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

Time Sheet
 

Glad to help - thanks for feeding back.

Pete

Kevin wrote:
Thanks for your help Pete

"Pete_UK" wrote:

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(D28,D2-8,0)

Hope this is what you want.

Pete

Kevin wrote:
Hi Pete

I do mean enter the word stat.

Stat in column B=8 hours in column E



"Pete_UK" wrote:

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

Kevin wrote:
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" wrote:

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(D28,D2-8,0)

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

Hope this helps.

Pete

Kevin wrote:
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

Time Sheet
 

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



"Kevin" wrote:

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