LinkBack URL
About LinkBacks
Example convert 3.03 to 03:03
(integer and decimal will never be >59. In other words this (70.64) will be an invalid number to convert.
Thanks
Example convert 3.03 to 03:03
(integer and decimal will never be >59. In other words this (70.64) will be an invalid number to convert.
Thanks
Assuming you have 3.03 in A2, you can use this:
=--SUBSTITUTE("0:"&A2,".",":")
Format the cell as mm:ss
Hope this helps.
Pete
EDIT: You may have to use a semicolon ( ; ) instead of the commas ( , )
Assuming 3.30 means 3 minutes and 30 seconds that formula returns 3:03.Originally Posted by Pete_UK
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you ALL for this plethora of answers !!!
It is 3.03 (3 minutes, 3 seconds) not 3.30
Yes, I know the sample number you showed us was 3.03 and the formula returns the correct result for that value.
However, if that value was 3.30 instead of 3.03 then the formula returns an incorrect result.
If 3.03 is 3 mins and 3 secs then I would assume 3.30 is 3 mins and 30 secs?
You can change the formula to this:
=--SUBSTITUTE("0:"&TEXT(A2,"0.00"),".",":")
to overcome that problem.
Hope this helps.
Pete
Nice catch !!!
Give this a try.
=IF(OR(LEFT($A$1,SEARCH(".",$A$1)-1)>"59",RIGHT($A$1,LEN($A$1)-FIND(".",$A$1))>"59"),"",LEFT($A$1,SEARCH(".",$A$1)-1)&":"&RIGHT($A$1,LEN($A$1)-FIND(".",$A$1)))
This should do it.
The formula will convert 3.03 to time value 03:03
Formula:
=DOLLARDE(A1,60)/1440
format cell B1 as Custom, mm:ss
v A B 1 3.03 03:03
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Maybe this will help
Decimal time in A1. Enter in B1 and format as mm:ss
Formula:=(1/86400)*(--LEFT(A1,FIND(".",A1)-1)*60+(--RIGHT(A1,2)))
To return a maximum of 59:59 format as mm:ss
Formula:=MIN((1/86400)*(--LEFT(A1,FIND(".",A1)-1)*60+(--RIGHT(A1,2))),0.04165)
If you want 0 returned if greater than 59:59
Formula:=IF((1/86400)*(--LEFT(A2,FIND(".",A2)-1)*60+(--RIGHT(A2,2)))>0.04165,0,1/86400)*(--LEFT(A2,FIND(".",A2)-1)*60+(--RIGHT(A2,2)))
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
or try... =TIME(0,INT(A2),MOD(A2,1)*100)
Yep! This works too!
Thx
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks