Duration stored as text
 

I have several thousand rows of data with time duration stored as
text. For example, "00:30:16" is 30 minutes, 16 seconds. Is there any
way I can convert this to a number, so that I can run calculations on
this time duration?

If I can't, I'll use MATCH and MID to slice up the text into three
separate columns (hours, minutes, seconds), then multiply the first
two appropriately and add them all up as seconds to get the total. I
*really* don't want to do that, though. Please tell me Excel has
something clever I just wasn't aware of yet.

Thanks!

Duration stored as text
 

On Jul 5, 4:48 pm, test me wrote:
I have several thousand rows of data with time duration stored as
text. For example, "00:30:16" is 30 minutes, 16 seconds. Is there any
way I can convert this to a number, so that I can run calculations on
this time duration?

If I can't, I'll use MATCH and MID to slice up the text into three
separate columns (hours, minutes, seconds), then multiply the first
two appropriately and add them all up as seconds to get the total. I
*really* don't want to do that, though. Please tell me Excel has
something clever I just wasn't aware of yet.

Thanks!

The match/mid wasn't as bad as I thought, since the time duration was
already formatted to have the same number of digits. So I just used

=MID(duration, 1, 2)*3600+MID(duration, 4,2)*60+MID(duration,7,2)

to get the amount of time in seconds.

But if there is a function that does this, I'd like to hear about it.
Thanks!

--Rachel