View Full Version : Spreadsheet Question - how to total a Time column
I've got a column of hours and minutes (00:00) I'm trying to total. Each cell is the result of a start and end time that works fine.
What I can't get is a result once the total exceeds 24 hrs. I've tried -
<ul type="square"> =SUM(C.1:C50) [/list]
and get "#####" as a result.
I checked the first few cells (less than 24 hrs total) and it works.
Any suggestions? Thanks.
/images/graemlins/confused.gif
SamIAm
12-15-2005, 12:56 PM
It's not that your column is too narrow, is it?
-Sam
CrashPat
12-15-2005, 02:29 PM
Try changing the format of the cells. I use openoffice, not excel so it might be different but if I change from the default formatting to [HH]:MM:SS it works. The brackets around the HH allow it to go past 24 hours, if I leave those off it cycles on the 24 hour clock, so 23:45 + 1:15 results in 01:00 instead of 25:00. You also need to make sure that the column is wide enough to display the result or it will be ###.
Timer
12-15-2005, 02:37 PM
[ QUOTE ]
I've got a column of hours and minutes (00:00) I'm trying to total. Each cell is the result of a start and end time that works fine.
What I can't get is a result once the total exceeds 24 hrs. I've tried -
<ul type="square"> =SUM(C.1:C50) [/list]
and get "#####" as a result.
I checked the first few cells (less than 24 hrs total) and it works.
Any suggestions? Thanks.
/images/graemlins/confused.gif
[/ QUOTE ]
Make sure the format is :
right click the cell>format cells>time> then select (on office 2000 sixth one down or 37:30:55
Try this first, if that doesn't work, I'll try something else.
SamIAm
12-15-2005, 03:21 PM
[ QUOTE ]
Try this first, if that doesn't work, I'll try something else.
[/ QUOTE ]I'm going to add this to the bottom of all my posts.
-Sam
[ QUOTE ]
It's not that your column is too narrow, is it?
-Sam
[/ QUOTE ]
That was my immediate first thought, but no, column width didn't correct it.
Thank you. This might be the answer. I'll try it and let you know. Again, muchos gracias. And I'm using OpenOffice 2.0
Big thanks to you, too. That's another possibility.
[ QUOTE ]
Try changing the format of the cells.
[/ QUOTE ]
ka-ching! problem go bye-bye.
/images/graemlins/smile.gif
Timer
12-15-2005, 09:26 PM
If you have further problems with Excel, including complicated formula questions you can go to this newsgroup and get good answers quickly:
microsoft.public.excel.misc
Thanks for that. MS's site is so friggin' big I get lost trying to find something there. This pinpoints me now.
/images/graemlins/smile.gif
Dan Mezick
12-16-2005, 11:39 AM
Express tim as fractions of an hour (N.NN)
(Stop-start)/60 = time expressed in hours.
10:15 stop, 9:30 start = 45/60 = .75 (do this by hand at the end of the session)
Put the resulting .75 in the column for elapsed time.
Then use SUM() to get your total SUM() hours. I use this method for summing time.
By the way if you get "#####" for the result try widening the column. The #### usually means not enough width available to express the full value. Your result may have many decimal places that is causing this effect.
Thanks for the reply. Yeah, knew about the column width. Only recently figured out the start/stop thingie.
Appreciate your input.
vBulletin® v3.8.11, Copyright ©2000-2024, vBulletin Solutions Inc.