Need help formatting time.

Shri

New member
Joined
Nov 7, 2013
Messages
6
Reaction score
0
Points
0
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. :heh:

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


 
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.
 
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?)
 
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.
 
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
 
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.
 
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)
 
Back
Top