LinkBack URL
About LinkBacks
I have a log that imports a duration as '9 s.' without the quotes of course, and if it exceeds seconds it will be 1.2 min. Is there any way to convert this via formula or other method to display it as mm ss ?? Any help would be GREATLY appreciated!
I have a log that imports a duration as '9 s.' without the quotes of course, and if it exceeds seconds it will be 1.2 min. Is there any way to convert this via formula or other method to display it as mm ss ?? Any help would be GREATLY appreciated!
If it's actually appearing in the cell as 9 s. it's probably a string (non-number). Does it always follow that format; i.e. 9 s. 22 s. 134 s. ?
If so, in a separate column (let's assume the data starts in A1),
Answer deleted, Terrible answer. Forget I said anything. LOL
Last edited by ChemistB; 04-05-2012 at 11:29 AM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
...and 1.2 minutes means 1 minute 12 seconds, right?
If it can only be one of those formats or the other then with data in A1 try this formula in B1
=SUBSTITUTE(SUBSTITUTE(A1,"s",""),"min","")/IF(COUNTIF(A1,"*min*"),1440,86400)
custom format B1 as mm:ss
Audere est facere
I attached a test file for example.
Yes 1.2 min equals 1 min 12 seconds
Will that formula still apply?
Dates Test.xlsx
It works for me with a small tweak (had to change s to s. and min to min.)
Put this formula in C2
=SUBSTITUTE(SUBSTITUTE(A2,"s.",""),"min.","")/IF(COUNTIF(A2,"*min*"),1440,86400)
format as mm:ss and then copy to D2 and down both columns
That worked! You guys are geniuses! Thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks