MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Time Sheet Problem


Posted by Percy on February 08, 2002 7:31 AM

Here goes. Below is a portion of a Timesheet WHERE Column a1 is "Monday", etc. across
Cells a3 - a6 are clock numbers, ( TIMEValues ).

a1=|Monday|Tuesday|Wednesday|Thursday|Friday| | | |
a2=|02/11 |02/12/ |02/13/ |02/14/ |02/15 | | | |
a3=|8:00 |8:00 |8:00 |8:00 |8:00
a4=|12:00 |12:00 |12:00 |12:00 |12:00
a5=|12:30 |12:30 |12:30 |12:30 |12:30
a6=|16:30 |16:30 |16:30 |16:30 |16:30
a7=|8:00 |8:00 |8:00 |8:00 |8:00

ROW a7 is all Formulas as written below. This works.
a7=SUM(a6-(a3+SUM(a5-4)))

A bit further down, I cannot seem to get a formula written in CELL a12 to TOTAL the hours Across ROW (a7:e7), in this instance, 40 hrs.
The formula =sum(a7:e7) returns a value of 16.
I've looked around a bit and haven't seen the answer.
This is probably very simple but I cannot see it.
Help please?

Thanks very.


Posted by lenze on February 08, 2002 7:59 AM

Hi Percy:
Take the formula in Row 7 and multiply it by 24. Change the formatting of the cells to General of Number. This will convert the result into actual hours which you can add and subtract.
Example:
7a=SUM(a6-(a3+SUM(a5-a4)))*24

Posted by Percy on February 08, 2002 8:16 AM

Thanks, very much. Worked like a charm.
I also need to convert the cell totaling the hours to a Number from a Time format.
Again, thanks a lot. {:>)