Summing Time
Posted by Mike on April 25, 2001 10:54 AM
I am making a spreadsheet (timesheet) in Excel 97 that calculates total time taken per contract and having major problems figuring out why my hour summation doesn't work.
I am using Validation lists for the time columns, they are formatted in hh:mm. I subtract A2 from B2 and get the proper total in rows. The problem
comes when I try to total the columns. I have used [h]:mm and the ()*24 and nothing works correctly ... I am at a loss and need assistance. Probably
something silly I am overlooking but I have tried multiple formating and cell properties with nothing working.
Example:
N11=(M11-L11) = 15:00
N12=(M12-L12) = 18:30
then
=SUM(N11:N27) = 28113:30 if [h]:mm
=SUM(N11:N27)*24 = 674724:00 if [h]:mm
hhmm = 0930
I've tried all I thought would work and now trying anything I can think of to get 33:30 ......... help please.
TIA
Posted by Mark W. on April 25, 2001 12:19 PM
Use the array formula, {=SUM(N11:N27/24)*24}, and
format it as... [h]:mm
Posted by Mark W. on April 25, 2001 1:25 PM
What'a minute...
Mike, I didn't double-check your claim that
=SUM(N11:N27) wouldn't work until just now.
It works fine! There's no need for my
suggested formulation. Just use =SUM(N11:N27)
and format it as... [h]:mm
Posted by Mike on April 26, 2001 5:40 AM
Re: What'a minute...
Thanks for the assistance ..... the problem stemmed from cell formatting.
Even though I tried to get the cell to use time only, the date and time were being used.
Instead of 0500 being 5:00:00 AM in the formula display it was 01/01/1991 5:00 AM or something like that.
Once I killed the date all worked fine.
Thanks again for the help .... back to lurking ;)
