Friday, September 15, 2017

Convert ##m ##s formatted text to a number in seconds



I'm hoping to find some way to convert this data into numbers so I can use those numbers for other formulas. I have checked the reporting system of the program I am pulling this info from and I have no choice but to pull this data in this format.

Here's the raw data:

NameResponse TimeDuration
Agent A1m 58s19m 27s
Agent B1m 3s18m 7s
Agent C49s16m 27s

and I would like to automatically convert it to this format:

NameResponse TimeDuration
Agent A118.001167
Agent B63.001087
Agent C49.00987
Can someone suggest a formula to do this? I have attempted to change the cells from "Automatic" to "Duration" or "Number", and nothing actually converts the numbers at all.


If you have following data in Sheet1:

Then you can try the following formula in cell B2 of "Sheet2":


No comments:

Post a Comment