Hi again


"Shandy720" wrote
in message ...

thanks avri but i dont seem to have the choice for that format.
I can convert it to (h) mm:ss but ideally do not want to have seconds
shown.

The trouble i am having is regarding adding hours up in the first
place. I have columns of hours worked each day and sum this for the
weekly total. However using an hh:mm format does not allow me to sum it
up properly. For instance 9:10+9:10+9:10+9:10 equals 12:40.

Format the cell with sum as "[h]:mm" , and same result is displayed as
36:40. To understand why, format same cell as "d h:mm" - now 1 12:40 is
displayed.

The reason for such behaviour is the way dates and times are stored in
Excel. Format the same cell with sum as Numeric or General - 1.5277778 id
displayed. Integer part of this number is for days (time intervals 24 hours
long), decimal part is for hours, minutes and seconds, and is calculated as
1/24 of hour. I.e. 12 hours and 40 minutes is stored as
(12+40/60)/24~0.5277778

Using square bracets around leftmost part of time format string supresses
24-hour or 60-minute or 60-second rollower. I.e formats "[h]", "[h]:mm:ss",
"[m]:ss" etc. are supported.


Arvi Laanemets