Duration stored as text

  • Thread starter Thread starter test me
  • Start date Start date
T

test me

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!
 
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!
Click to expand...

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
 
Back
Top