Need help formatting time.

[Shri]

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.

[NBVC]

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.

[Shri]

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?

[NBVC]

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.

[NBVC]

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?)

[Shri]

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.

[NBVC]

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

[Shri]

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.

[NBVC]

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

Let's try this:

=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)