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?

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


On Jan 5, 12:31*pm, Paul wrote:
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?

Thanks Pete this works perfectly , much appreciated

Paul

"Pete_UK" wrote:

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


On Jan 5, 12:31 pm, Paul wrote:
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?

You're welcome, Paul - thanks for feeding back.

Pete

On Jan 5, 1:45*pm, Paul wrote:
Thanks Pete this works perfectly , much appreciated

Paul

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

"Paul" wrote:

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?

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:

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?

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

On Jan 5, 12:31*pm, Paul wrote:
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?

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).
--
David Biddulph

"Paul" wrote in message
...
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?

Hi

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

HTH. Best wishes Harald

"Paul" wrote in message
...
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?