I am trying to construct a worksheet that will allow me to figure out my
hours worked, regular time and overtime in seperate columns. I have the
following headings:

Start End Hours Reg Hours Overtime Total
F 1 8:00 AM 9:00 PM 13:00

F = Friday
1 = 1st of the month

I want to know hot to set it up to figure out the Reg Hours column, Overtime
= anything after 8 hours and the total should be the same number as in the
hours column

Hi!

Having both an Hours column and a Total column is kind of redundant isn't
it?

Headers in row 1 from C1 to G1

C2 = 8:00 AM
D2 = 9:00 PM

E2 = formula for Hours:

=IF(D2="","",(D2-C2+(D2<C2))*24)

F2 = formula for Reg Hours:

=IF(D2="","",MIN((D2-C2+(D2<C2))*24,8))

G2 = formula for OT Hours:

=IF(D2="","",MAX(0,(D2-C2+(D2<C2))*24-8))

H2 = formula for Total:

=IF(D2="","",SUM(F2:G2))

Select the range E2:H2 and copy down as needed.

Biff

"bugged out" wrote in message
...
I am trying to construct a worksheet that will allow me to figure out my
hours worked, regular time and overtime in seperate columns. I have the
following headings:

Start End Hours Reg Hours Overtime Total
F 1 8:00 AM 9:00 PM 13:00

F = Friday
1 = 1st of the month

I want to know hot to set it up to figure out the Reg Hours column,
Overtime
= anything after 8 hours and the total should be the same number as in the
hours column

Hi

And if you want to be able to take time off to compensate for overtime
worked, merely change the formula in G2 to read

=IF(D2="","",MAX(0,(D2-C2+(D2<C2))*24-8))

If that is what you want to achieve, I would do away with the second Total
column, and instead insert the following formulae in H2 and H3, while I would
insert Cum Total as a heading in H1

H2 =F2
H3 =IF(D3<"",H2+F3,"")
That way, you know how many hours overtime a guy has worked, and how much
remains after taking time off.
--
ve_2nd_at. Randburg, Gauteng, South Africa


"Biff" wrote:

Hi!

Having both an Hours column and a Total column is kind of redundant isn't
it?

Headers in row 1 from C1 to G1

C2 = 8:00 AM
D2 = 9:00 PM

E2 = formula for Hours:

=IF(D2="","",(D2-C2+(D2<C2))*24)

F2 = formula for Reg Hours:

=IF(D2="","",MIN((D2-C2+(D2<C2))*24,8))

G2 = formula for OT Hours:

=IF(D2="","",MAX(0,(D2-C2+(D2<C2))*24-8))

H2 = formula for Total:

=IF(D2="","",SUM(F2:G2))

Select the range E2:H2 and copy down as needed.

Biff

"bugged out" wrote in message
...
I am trying to construct a worksheet that will allow me to figure out my
hours worked, regular time and overtime in seperate columns. I have the
following headings:

Start End Hours Reg Hours Overtime Total
F 1 8:00 AM 9:00 PM 13:00

F = Friday
1 = 1st of the month

I want to know hot to set it up to figure out the Reg Hours column,
Overtime
= anything after 8 hours and the total should be the same number as in the
hours column