How do I get the sum in hours and minutes for a range of fields that
are formatted as "time" (hh:mm)?

I have two columns, one with starting time, one with ending time and a
third column that calculates the difference between ending and
starting time.
I can sum up the third column, but only until it exceeds 24 hours.

I understand that hours are handled like fraction of a day and I guess
I have to convert that fraction into a number.

At the same time I would have to keep the minutes as a fraction of an
hour, because otherwise the summing up would be confused?

TIA
Örjan Skoglösa

Orjan,

Use a custom number format of [hh]:mm . The brackets instruct Excel not to
"roll over" the displayed time at 24 hours.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Örjan Skoglösa" wrote in message
...
How do I get the sum in hours and minutes for a range of fields that
are formatted as "time" (hh:mm)?

I have two columns, one with starting time, one with ending time and a
third column that calculates the difference between ending and
starting time.
I can sum up the third column, but only until it exceeds 24 hours.

I understand that hours are handled like fraction of a day and I guess
I have to convert that fraction into a number.

At the same time I would have to keep the minutes as a fraction of an
hour, because otherwise the summing up would be confused?

TIA
Örjan Skoglösa

Hi Chip,

So easy. And so difficult.

Works like a charm.

Thanks a lot.

Örjan


On Wed, 1 Oct 2003 08:54:51 -0500, "Chip Pearson"
wrote:

Orjan,

Use a custom number format of [hh]:mm . The brackets instruct Excel not to
"roll over" the displayed time at 24 hours.

Orjan,

I'm glad it works. You may be interested in another number format, [mm]:ss,
that you can use to prevent Excel from rolling over minutes at 60 minutes.
For example, a cell formatted with [mm]:ss will display 70:30 rather than
1:10:30 for 70 minutes and 30 seconds.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Örjan Skoglösa" wrote in message
...
Hi Chip,

So easy. And so difficult.

Works like a charm.

Thanks a lot.

Örjan


On Wed, 1 Oct 2003 08:54:51 -0500, "Chip Pearson"
wrote:

Orjan,

Use a custom number format of [hh]:mm . The brackets instruct Excel not
to
"roll over" the displayed time at 24 hours.