Formatting a weekly time sheet

S

Sparky

I'm a newbie using excel 2003, and trying to format a weekly time sheet that
totals the hours worked for several different activities. I would like to
format two more cells labeled "regular hours" and "overtime hours" so that
the total of hours worked up to and including 40 hours appears in one, and
the total of hours worked over 40 appears in the other.
For example, J30 has the formula =SUM(J4:J29). When that total exceeds 40,
how do I format D32 to include the hours up to and including 40, and format
D34 to include everything over 40?
 
C

Chip Pearson

The following formulas will give you regular hours and overtime hours
given a total number of hours in cell A1.

=MIN(A1,40)
will return the regular hours, up to a maximum of 40.

=MAX(0,A1-40)
will return the overtime hours. If A1<40, it returns 0.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top