One of my data sheets at work brings numbers into Excel as decimals. For
instance, 5 hours and 39 minutes is shown as 5.39. How can I get Excel to
convert this to 5:39 (semicolin instead of decimal)?

On 25 Sep, 14:12, mailrail wrote:
One of my data sheets at work brings numbers into Excel as decimals. For
instance, 5 hours and 39 minutes is shown as 5.39. How can I get Excel to
convert this to 5:39 (semicolin instead of decimal)?

Hello

Assuming your value in A1 then you can use the following general
purpose formula (and copied down as far as you have data):

=(A1+40/60*MOD(A1,1))/24

Richard

One way (assuming the number is in A1):

=(INT(A1) + MOD(A1,1)*100/60)/24

Format the cell in an appropriate time format.

Hope this helps.

Pete

On Sep 25, 2:12 pm, mailrail
wrote:
One of my data sheets at work brings numbers into Excel as decimals. For
instance, 5 hours and 39 minutes is shown as 5.39. How can I get Excel to
convert this to 5:39 (semicolin instead of decimal)?

XL stores times as fractional days. So to convert to hours, enter 24 in
a blank cell. Copy the cell. Select the cells with your times, and
choose Edit/Paste Special, selecting the Values and Divide radio
buttons. Click OK.

Format as times.

In article ,
mailrail wrote:

One of my data sheets at work brings numbers into Excel as decimals. For
instance, 5 hours and 39 minutes is shown as 5.39. How can I get Excel to
convert this to 5:39 (semicolin instead of decimal)?

Just highlight the cells you want changed (or entire worksheet by pressing
the grey box between Row 1 and column A). the go to edit Menu - Replace.
Enter in From the period and the to box a colon. Press replace all.

"mailrail" wrote:

One of my data sheets at work brings numbers into Excel as decimals. For
instance, 5 hours and 39 minutes is shown as 5.39. How can I get Excel to
convert this to 5:39 (semicolin instead of decimal)?

That did it! Thanks!

"RichardSchollar" wrote:

On 25 Sep, 14:12, mailrail wrote:
One of my data sheets at work brings numbers into Excel as decimals. For
instance, 5 hours and 39 minutes is shown as 5.39. How can I get Excel to
convert this to 5:39 (semicolin instead of decimal)?

Hello

Assuming your value in A1 then you can use the following general
purpose formula (and copied down as far as you have data):

=(A1+40/60*MOD(A1,1))/24

Richard

mailrail: After reading all the responses to this posting I think the problem
is more complicated. Can you explain "brings numbers into Excel as decimals"
in more details. I suspect there may be a macro running that need to be
fixed. Or you are importing the data and using the wrong options. Explain
whre the source data is coming from. Post a sample of the data that you are
importing.

The responjses are not solving the root cause of the problem, just creating
a work-around for the problem.

"mailrail" wrote:

That did it! Thanks!

"RichardSchollar" wrote:

On 25 Sep, 14:12, mailrail wrote:
One of my data sheets at work brings numbers into Excel as decimals. For
instance, 5 hours and 39 minutes is shown as 5.39. How can I get Excel to
convert this to 5:39 (semicolin instead of decimal)?

Hello

Assuming your value in A1 then you can use the following general
purpose formula (and copied down as far as you have data):

=(A1+40/60*MOD(A1,1))/24

Richard