decimal time conversions

P

Paul

Hi

I have decimal numbers that need to be converted to times eg 18.76 = 18 mins
46 seconds using the text formaula but I have 26.35 that equals 2.21. Why can
anyone help?
 
P

Pete_UK

With your decimal time in A1, use this in B1:

=A1/60/24

and format the cell as [mm]:ss

If you want the result as a text value, you can use this:

=TEXT(A1/60/24,"[mm]:ss")

Hope this helps.

Pete
 
J

Joel

Not sure where your number are coming from. I used

=TIMEVALUE("00:26:35")

The results is 0.018461 which equals

26/(24*60) + 35/(24*60*60)


Time values is in Hours:Minutes:Seconds.

1 hour = 1/24 since a day equals 1.00

1 Minute = 1/(24*60) 24 hours/day * 60 minutes/hour

1 Second = 1/(24*60*60) 24 hours/day * 60 minutes/hour * 60 seconds/minute




which gives
 
S

Stefi

I can't explain it without seeing the formula, but
=TIME(0,INT(A4),MOD(A4,1)*60)
and formatting the rsult cell like mm:ss
gives correct results.

Regards,
Stefi

„Paul†ezt írta:
 
P

Pete_UK

I suspect the 26.35 is being interpreted as 26.35 hours, which means
26 hrs and 21 minutes, and is being displayed as 2 hrs 21 mins (2:21)
because it is wrapping at 24 hours.

Hope this helps.

Pete
 
D

David Biddulph

You forgot to tell us *which* text formula you are trying to use.
It sounds as if you've got at least 3 problems.
26.35 minutes is 26:21 in minutes and seconds, so if you are seeing 2.21 it
sounds as if you are treating the number as hours and minutes, rather than
minutes and seconds (so you may need another divide by 60, but it may not
matter drastically if you are giving a text output and format it
appropriately), and also you have a format like h.mm, giving 2.21 (not
giving the 24 hours from a whole day), whereas [h].mm would give you 26.21.
I would also recommend using [h]:mm instead of [h].mm, or [m]:ss instead of
[m].ss, as the colon makes it clearer that you've got hours and minutes
(like 26:21), or minutes and seconds, rather than decimal hours and decimal
minutes (26.21).
 
P

Paul

Thanks Pete this works perfectly , much appreciated

Paul

Pete_UK said:
With your decimal time in A1, use this in B1:

=A1/60/24

and format the cell as [mm]:ss

If you want the result as a text value, you can use this:

=TEXT(A1/60/24,"[mm]:ss")

Hope this helps.

Pete


Hi

I have decimal numbers that need to be converted to times eg 18.76 = 18 mins
46 seconds using the text formaula but I have 26.35 that equals 2.21. Why can
anyone help?
Click to expand...
Click to expand...
 
H

Harald Staff

Hi

Divide by 24 and format as time (2:21, note the colon).

HTH. Best wishes Harald
 
Top