# Thread: Need help formatting time.

1. ## Need help formatting time.

Guys,

I am new to excel..

I got cells with text entries like "1 Min 30 Secs" or "36 Secs". Is there a way where excel can convert 1 Min 30 Secs to 90 seconds? The name of the row is Duration, and I got around 50,000 such rows to convert.

Here is what my sheet looks like. Any help is appreciated.

 Duration 5 Secs 6 Secs 13 Secs 14 Secs 6 Secs 24 Secs 24 Secs 3 Secs 1 Min 58 Secs 4 Secs 12 Secs 17 Secs 11 Secs

Cheers
Shri.

2. Assuming the data is in column A, starting at A2, then in B2 enter formula:

=IF(ISNUMBER(SEARCH("Min",A2)),LEFT(A2,SEARCH("Min",A2)-1)*60+SUBSTITUTE(MID(A2,SEARCH("Min",A2)+4,255),"Secs","")&" Secs",A2)

copied down.

You can then copy and paste special >> Values over the original if desired and remove the formula column.

3. Thank you a Ton NBVC,.. it worked like charm. The out put prints with Secs attributes, i.e 1 Min 48 Secs is calculated as 108 Secs. Any idea if it can just give 108 instead of 108 secs? Also, what if I have some entries like 2 hrs 30 mins 20 secs?

4. So you just want total seconds for all cells without the "Secs" at the end?

Also, you should always list a representative sampling of all possibilities... adding hours adds complexity.

5. Also will there be a possibility of an entry like: 5 Hrs 0 Mins 10 Secs, or 2 Min, or 5 Hr 7 min? (i.e. will the Mins or Secs ever be "missing" if they are 0?)

6. It will be missing. The data I am feeding is a constant variable.. i.e, I may get 5 hrs 2 Mins 3 secs, or 1 Min 58 Secs, or just Secs. I may also get 5 hrs 4 Secs. !!!.. Sounds weird.

7. Hopefully this will work...

=IFERROR(MID(" "&SUBSTITUTE(A2," "," "),SEARCH("Hr"," "&SUBSTITUTE(A2," "," "))-10,5)*3600,0)+IFERROR(MID(" "&SUBSTITUTE(A2," "," "),SEARCH("Min"," "&SUBSTITUTE(A2," "," "))-10,5)*60,0)+IFERROR(MID(" "&SUBSTITUTE(A2," "," "),SEARCH("Sec"," "&SUBSTITUTE(A2," "," "))-10,5)+0,0)

copied down

8. I just got the actual data. Here are 3 combinations for converting to Secs.

Combination 1 : 7 Secs
Combination 2: 3 Mins 1 Secs

*=IF(ISNUMBER(SEARCH("Min",A2)),LEFT(A2,SEARCH("Min",A2)-1)*60+SUBSTITUTE(MID(A2,SEARCH("Min",A2)+4,255),"Secs","")&" Secs",A2)

The above formula works just fine for combination 1 and 2.
===============================================================

Combination 3: 1 Hr 30 Mins
I changed my formula like below and fails to convert 1 Hr 30 Mins to Secs.

=IFERROR(MID(" "&SUBSTITUTE(H2," "," "),SEARCH("Hr"," "&SUBSTITUTE(H2," "," "))-10,5)*3600,0)+IFERROR(MID(" "&SUBSTITUTE(H2," "," "),SEARCH("Mins"," "&SUBSTITUTE(H2," "," "))-10,5)*60,0)+IFERROR(MID(" "&SUBSTITUTE(H2," "," "),SEARCH("Secs"," "&SUBSTITUTE(H2," "," "))-10,5)+0,0)

Sorry guys, I am new to excel and not sure what I miss.

9. I didn't notice, but I think the forum software trimmed all my spaces.

Let's try this:

Code:
`=IFERROR(MID("     "&SUBSTITUTE(A2," ","     "),SEARCH("Hr","     "&SUBSTITUTE(A2," ","     "))-10,5)*3600,0)+IFERROR(MID("     "&SUBSTITUTE(A2," ","     "),SEARCH("Min"," "&"     "&SUBSTITUTE(A2," ","     "))-10,5)*60,0)+IFERROR(MID("     "&SUBSTITUTE(A2," ","     "),SEARCH("Sec"," "&"     "&SUBSTITUTE(A2," ","     "))-10,5)+0,0)`

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•