Converting :mm:ss to ss

[Phredd]

I have data that I export to Excel where the total time comes in as :mm:ss.
I need to convert that format into seconds. For example, my report shows me
:01:25, I need to be able to show that at 85 seconds. I have a formula that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100) where
G2 is 01:25 the result will be 85. but the report i have now comes with an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for the
extra :. I am sure this is pretty simple it is just beyone me though. Any
help will be much appreciated!
Phredd

 

[David Biddulph]

Your formula won't work for 01:25 (try it!), but it would give a result of
85 if the G2 had contained 01.25 instead of 01:25.

For your text input of :01:25, you need =60*MID(G2,2,2)+RIGHT(G2,2) or
otherwise =RIGHT(G2,5)*24*60

 

[Rick Rothstein \(MVP - VB\)]

Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the concatenation
is multiplied by it, the concatenation is turned into an actual time value
(because your value has a time format except for the missing hours part...
which the concatenation puts in).

Rick

 

[David Biddulph]

In a day, rather than in a year, I hope, Rick?

 

[Rick Rothstein \(MVP - VB\)]

When you get older, like me, time appears to go faster... it **seemed** like
a year to me<g>; but yes, you are right, there are 86400 seconds in a
**day**, not a year (of course, the formula still works correctly, in spite
of this misstatement in my attempt to explain why). Thanks for noting that.

Rick

 

[Phredd]

Rick,

Thank you so much for your help that worked great! I did discover another
problem i could use anyones' help with related to the same thing. Some of my
data is reflected as h:mm:ss, (which i didn't discover last night) and the
formula you provided to me returns a result of #VALUE for those cells. is
there a way also capture the h: also in a ss format? for example 1:01:23 as
3683.

Phredd.

 

[Rick Rothstein \(MVP - VB\)]

My fault... I should not have used two zeroes in the concatenation... one
zero would have been enough for your original problem AND would also work
for the entries you just wrote about as long as you NEVER more than 9 in the
hours position (that is, either an entry like your original post showed or,
if an hour is present, the time value is 9:59:59 or less).

=86400*("0"&G2)

Rick

 

[David Biddulph]

If you want to cope both with 1:01:23 and :01:23, try
=IF(LEN(A2)>6,A2,RIGHT(A2,5)/60)*24*3600

 

[Phredd]

Rick,

Thanks, both you and David are awesome! Thank you so much. You guys are
Excel Rock Stars!

Phredd.

 

[Phredd]

David,

Thank you so much for your help. I am just amazed at how much knowledge is
out there. Thanks for sharing. I wish I could buy you both a drink for your
help.

Phredd.