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"


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Shandy720" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
>
>
> --
> Shandy720
> ------------------------------------------------------------------------
> Shandy720's Profile:
> http://www.excelforum.com/member.php...o&userid=26230
> View this thread: http://www.excelforum.com/showthread...hreadid=474998
>

[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 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.
Therefore i convert the hours worked into a decimal so they can be summed and then need to convert them back into a total weekly hours 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



"Shandy720" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
> Therefore i convert the hours worked into a decimal so they can be
> summed and then need to convert them back into a total weekly hours
> worked later in the hh:mm original format!!!!
>
> I hope this makes sense and many thanks for your help.
>
>
> --
> Shandy720
> ------------------------------------------------------------------------
> Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
> View this thread: http://www.excelforum.com/showthread...hreadid=474998
>

[Arvi Laanemets]

Hi again


"Shandy720" <[email protected]> wrote
in message news:[email protected]...
>
> 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