Converting decimals into hh:mm

[Shandy720]

Hi, can someone please help.

I have employees data regarding hours worked in a decimal format but
need to convert this to show hh:mm. So that 3.5 becomes 3:30.
I have tried dividing the original decimal by 24 and reformatting but
this only works for figures up to 24. How would I be able to say covert
103.25 hours into 103:15.
Is there a way of doing this?

Many thanks, Andrew

 

[Arvi Laanemets]

Hi

The formula is same, but use format "[h]:mm"

 

[Shandy720]

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 second
shown.

The trouble i am having is regarding adding hours up in the firs
place. I have columns of hours worked each day and sum this for th
weekly total. However using an hh:mm format does not allow me to sum i
up properly. For instance 9:10+9:10+9:10+9:10 equals 12:40.
Therefore i convert the hours worked into a decimal so they can b
summed and then need to convert them back into a total weekly hour
worked later in the hh:mm original format!!!!

I hope this makes sense and many thanks for your help

 

[Arvi Laanemets]

Hi

Simply select Custom format, and enter the format string into Type field.


Arvi Laanemets

 

[Arvi Laanemets]

Hi again

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