How do I set up a time card format in Excel

[Guest]

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

 

[Biff]

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

 

[Guest]

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.