Wacky time calculation

M

mailrail

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)?
 
R

RichardSchollar

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)?
Click to expand...

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
 
P

Pete_UK

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
 
J

JE McGimpsey

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.
 
J

Joel

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.
 
M

mailrail

That did it! Thanks!

RichardSchollar said:
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
Click to expand...
 
J

Joel

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.
 
Top