Adding (Sum of) Hours...

[Moonchild]

I'm sorry to ask such a seemingly trivial (!) question here..
I *have* Googled and XL Helped but cannot get this to work and I'm on a
tight timeframe.

I'm trying to add a simple column of hours (hh:mm) worked but simply can't
find the right combination of cell formatting to give a straight answer!
The hours are actually in non-adjacent cells (a3:c3:e3 etc) but if I can get
a column addition working, I'll transfer the formula.
eg.

6:15
8:00
6:30
9:00

TOTAL =SUM(A1:A4)

I understand it's a formatting issue but I've just spent an hour+ trying to
resolve this.
The 'Total' is always 0 (Zero)

Please help!
Thanks.

 

[Pete_UK]

Select the cell with the formula in and Format | Cells | Number (tab)
then choose Custom (near the bottom of the list) - type the following
directly into the panel:

[h]:mm

This will display hours above 24 without wrapping into days.

Another way is to convert the total into decimal hours, and format the
cell as number with 2 dp:

=your_addition_ formula * 24

Obviously, a value of 37.25 (for example) represents 37 hours and 15
minutes in this format.

Hope this helps.

Pete

 

[Earl Kiosterud]

Moon,

To keep hours from rolling into days, use Format - Cells - Custom tab:

[hh] will show 25 hours 30 minutes as 25
[hh]:mm will show it as 25:30
hh:mm will show it as 01:30 (just the rollover from 24 hours)

Or you can multiply your result by 24, converting it to straight hours, and
formatting it for regular numbers (Format Cells - Number: General, Number,
etc)

=SUM(A3, C3, E3) * 24

 

[Earl Kiosterud]

Pete,

It appears I've plagiarized your post. I wonder how I missed it. If we
posted at similar times it could happen -- the Microsoft news server farm
has quite a bit of latency sometimes. But if you're in the UK, then your
posting time should be later than mine, not earlier, to be concurrent. Or I
wonder if you're on the West coast USA (I'm on the East coast).

Maybe I just didn't see it. Sheesh, it's bad enough that posters put the
same question in multiple newsgroups, getting responders to work on
questions already answered. Or sometimes even in the same group, separated
only by hours.

Oh, well. You were there first. Where's the damned coffee?
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Select the cell with the formula in and Format | Cells | Number (tab)
then choose Custom (near the bottom of the list) - type the following
directly into the panel:

[h]:mm

This will display hours above 24 without wrapping into days.

Another way is to convert the total into decimal hours, and format the
cell as number with 2 dp:

=your_addition_ formula * 24

Obviously, a value of 37.25 (for example) represents 37 hours and 15
minutes in this format.

Hope this helps.

Pete

I'm sorry to ask such a seemingly trivial (!) question here..
I *have* Googled and XL Helped but cannot get this to work and I'm on a
tight timeframe.

I'm trying to add a simple column of hours (hh:mm) worked but simply
can't
find the right combination of cell formatting to give a straight answer!
The hours are actually in non-adjacent cells (a3:c3:e3 etc) but if I can
get
a column addition working, I'll transfer the formula.
eg.

6:15
8:00
6:30
9:00

TOTAL =SUM(A1:A4)

I understand it's a formatting issue but I've just spent an hour+ trying
to
resolve this.
The 'Total' is always 0 (Zero)

Please help!
Thanks.

 

[Moonchild]

Moon,

To keep hours from rolling into days, use Format - Cells - Custom tab:

<snip>

Thank you both for the swift responses.

Earl, my problem wasn't the day rollover...it was getting an addition of any
sort. Answer always NIL.

I believe I've fixed it now - the problem was something to do with how I
arrived at the hours in each cell.
Simple subtraction of two times (in this case 'Work Start' and 'Work
Finish') giving a 'Time' format answer (which didn't want to be added to
other cells)

Converting the time differences to straight text (=a2-a1) instead of time
format yeilds a correct solution.
Strangely, converting them to time format AFTER gaining the solution
reformats the cells *without* changing the correct solution.

I don't understand it...but it works so I'm happy.

My thanks to Pete for giving me a pointer to this workaround...if that's
what it is!

Cheers...

 

[Pete_UK]

Hi Earl,

don't worry about it - I've found that some postings can take a
different amount of time to appear. I really am in the UK - have been
away for the last day and half. Incidentally, viewing this through
Google Groups, my posting was timed at 11:25am, your first one at
3:35pm and your second one at 4:55pm (my time), all yesterday.

Pete

Pete,

It appears I've plagiarized your post. I wonder how I missed it. If we
posted at similar times it could happen -- the Microsoft news server farm
has quite a bit of latency sometimes. But if you're in the UK, then your
posting time should be later than mine, not earlier, to be concurrent. Or I
wonder if you're on the West coast USA (I'm on the East coast).

Maybe I just didn't see it. Sheesh, it's bad enough that posters put the
same question in multiple newsgroups, getting responders to work on
questions already answered. Or sometimes even in the same group, separated
only by hours.

Oh, well. You were there first. Where's the damned coffee?
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Select the cell with the formula in and Format | Cells | Number (tab)
then choose Custom (near the bottom of the list) - type the following
directly into the panel:

[h]:mm

This will display hours above 24 without wrapping into days.

Another way is to convert the total into decimal hours, and format the
cell as number with 2 dp:

=your_addition_ formula * 24

Obviously, a value of 37.25 (for example) represents 37 hours and 15
minutes in this format.

Hope this helps.

Pete

I'm sorry to ask such a seemingly trivial (!) question here..
I *have* Googled and XL Helped but cannot get this to work and I'm on a
tight timeframe.

I'm trying to add a simple column of hours (hh:mm) worked but simply
can't
find the right combination of cell formatting to give a straight answer!
The hours are actually in non-adjacent cells (a3:c3:e3 etc) but if I can
get
a column addition working, I'll transfer the formula.
eg.

6:15
8:00
6:30
9:00

TOTAL =SUM(A1:A4)

I understand it's a formatting issue but I've just spent an hour+ trying
to
resolve this.
The 'Total' is always 0 (Zero)

Please help!
Thanks.

 

[Pete_UK]

Hi Moonchild,

glad you got it working - I'm not sure how you did it !! <bg>

Pete