Trouble converting a generic value for time (minutes and seconds) to a time value

Good Day,

I have a rather large data set that I am working with which includes values that are listed in a minutes/seconds format. The data was pulled from a csv file and is listed in generic text values but I would like to convert the data to be in time value (mm:ss).

For instance, here are 4 data points that I have:

Duration
2:10
1:00
0:45
0:55

*example... the first point is 2 minutes and 10 seconds.

When I attempt to sum up the totals or average them out, I am unable to do so since they are not currently listed as mm:ss values. How can I convert them in excel?

Additionally, I have a rather large data set to work with and will need to put them into a pivot table in order to get the larger total and average (among other key metrics). What value do I need to assign to the column in the pivot table in order to see the figures represented in mm:ss?

Thank you for your time... Your assistance is much appreciated!
jwberg95

Assuming the duration is in column D, you can use this formula in E2 (insert a new column E if you are already using it):

=VALUE("0:"&D2)

Format that cell as m:ss, then copy down, and this will convert your text values into times that Excel can work with. If you no longer need the values in column D, then you can fix the values in column E and then delete column D.

Hope this helps.

Pete

Thanks Pete!

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete