Wanted a formula to convert the below datas to as given in right hand side
Before formula After formula
0 0:9:8.0 9.8
0 0:9:7.0 9.7
0 0:9:7.0 9.7
0 0:9:7.0 9.7
0 0:9:59.0 9.59
0 0:9:59.0 9.59
0 0:9:58.0 9.58
0 0:9:58.0 9.58
0 0:9:58.0 9.58
0 0:9:58.0 9.58
0 0:9:56.0 9.56
0 0:9:56.0 9.56
0 0:9:55.0 9.55
0 0:9:55.0 9.55
Any help needed as i am newbie...
Try this :
=SUBSTITUTE(MID(A1,FIND("":"",A1)+1,FIND(""."",A1,FIND("":"",A1)+1)-FIND("":"",A1)-1),"":"",""."")
Last edited by JasperD; 07-09-2014 at 08:39 AM.
Please click the * below if this helps
Try this:
Before in A1, formula in B1 then drag it down.(double negative needed to convert to value)
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,":",".",2),":","")," ",""),".","",2)
This also works:
Formula:
Please Login or Register to view this content.
Last edited by newdoverman; 07-09-2014 at 09:40 AM.
<---------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
How about this:
Formula:Please Login or Register to view this content.
Please Login or Register to view this content.
Just this
=--SUBSTITUTE(A1,"0 0:","")
Format cell as Custom m:ss
0 0:9:8.0 0 0:9:7.0 0 0:9:7.0 0 0:9:59.0 0 0:9:59.0 0 0:9:58.0 0 0:9:55.0
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
A great thanks to all ... Excel Forum just rocks ...
But my confern is i wanted to calculate the average and these are the time. Can anyone help me to calculate average ?
Try this one
=AVERAGE(--SUBSTITUTE(A1:A7,"0 0:",""))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
0 0:9:8.0 0 0:9:7.0 0 0:9:7.0 0 0:9:59.0 0 0:9:59.0 0 0:9:58.0 0 0:9:55.0
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks