I would like to add hours in a worksheet to come up with a total number of
hours. However, when I add something like this:

5:00
5:00
5:00
5:00
5:30

.. . . the answer is 1:30. So excel is taking 25 hours and 30 minutes and
representing as 1:30.

How can I get excel to return the total hours total as hours (25:30)?

Thanks
--
William

You use the wrong format, use [h]:mm


--


Regards,


Peo Sjoblom



"William" wrote in message
...
I would like to add hours in a worksheet to come up with a total number of
hours. However, when I add something like this:

5:00
5:00
5:00
5:00
5:30

. . . the answer is 1:30. So excel is taking 25 hours and 30 minutes and
representing as 1:30.

How can I get excel to return the total hours total as hours (25:30)?

Thanks
--
William

They gave you the right answer, as far as it went, William, but maybe you'd
like to know WHY. In case you haven't figured it out already, Excel was
displaying the number of hours you get when you add those times: that is,
25:30 is one day, one hour and thirty minutes. But in the format for that
cell, Excel was displaying only the hours and minutes; probably the format
was set to "h:mm".

Excel gives you a lot of flexibility in displaying timestamps. Behind the
scenes what it had was 0.208333, 0.208333, 0.208333, 0.208333 and 0.229167
(all those are the correct fractions of days), and it added them up to 1.0625
days. You can display that in a number of ways:

Format string Resulting display
"d h:mm" 1 1:30
"yyyy-mm-dd" 1900-01-01
"yyyy-mm-dd hh:mm:ss" 1900-01-01 01:30:00
"ddd dd-mmm-yy hh:mm" Sun 01-Jan-00 01:30
"[h]:mm:ss" 25:30:00
"[hhh]:mm" 025:30
"dddd hh:mm" Sunday 01:30

....and so on. The trick is to realize that behind the scenes, Excel thinks
time began the midnight before Jan 0, 1900 and interprets all times as
durations from there, so 1.0625 days equals 1:30 in the morning of Sunday,
Jan 1, 1900. If you want to display the total hours, not just the time of
the last day of that "duration", you have to use a format string with square
brackets around the hour portion. (By that measure, it has been 953257:18:20
hours since the beginning of time as I write this.)

--- "William" wrote:
I would like to add hours in a worksheet to come up with a total number of
hours. However, when I add something like this:

5:00
5:00
5:00
5:00
5:30

. . . the answer is 1:30. So excel is taking 25 hours and 30 minutes and
representing as 1:30.

How can I get excel to return the total hours total as hours (25:30)?

Thank you all for the replies. Bob Bridges, thanks especially for taking the
time to provide the explanation. It was very much appreciated.

Best to all.
--
William


"Bob Bridges" wrote:

They gave you the right answer, as far as it went, William, but maybe you'd
like to know WHY. In case you haven't figured it out already, Excel was
displaying the number of hours you get when you add those times: that is,
25:30 is one day, one hour and thirty minutes. But in the format for that
cell, Excel was displaying only the hours and minutes; probably the format
was set to "h:mm".

Excel gives you a lot of flexibility in displaying timestamps. Behind the
scenes what it had was 0.208333, 0.208333, 0.208333, 0.208333 and 0.229167
(all those are the correct fractions of days), and it added them up to 1.0625
days. You can display that in a number of ways:

Format string Resulting display
"d h:mm" 1 1:30
"yyyy-mm-dd" 1900-01-01
"yyyy-mm-dd hh:mm:ss" 1900-01-01 01:30:00
"ddd dd-mmm-yy hh:mm" Sun 01-Jan-00 01:30
"[h]:mm:ss" 25:30:00
"[hhh]:mm" 025:30
"dddd hh:mm" Sunday 01:30

...and so on. The trick is to realize that behind the scenes, Excel thinks
time began the midnight before Jan 0, 1900 and interprets all times as
durations from there, so 1.0625 days equals 1:30 in the morning of Sunday,
Jan 1, 1900. If you want to display the total hours, not just the time of
the last day of that "duration", you have to use a format string with square
brackets around the hour portion. (By that measure, it has been 953257:18:20
hours since the beginning of time as I write this.)

--- "William" wrote:
I would like to add hours in a worksheet to come up with a total number of
hours. However, when I add something like this:

5:00
5:00
5:00
5:00
5:30

. . . the answer is 1:30. So excel is taking 25 hours and 30 minutes and
representing as 1:30.

How can I get excel to return the total hours total as hours (25:30)?