Converting TIME AM/PM to MM:SS

[trey213]

I will start with the obligatory statement of I am new to the forum and do not work with Excel very often…so here is what I want to do. I have a spreadsheet that is the resulting product of a radio programming software that exports music logs as .csv documents. I have imported/opened this spreadsheet in excel so I can track how many minutes are left in each hour that need to be filled to back-time each hour of the program. I need to make this extremely easy to use and read for the hosts of the show. So complicated formulas and maneuvers are out of the question.

Here is what we are working with in the spreadsheet we have times listed for song length in a column in this format "2:56" which represents two minutes and 56 seconds total song length. However, Excel is reading this data as "2:56 AM" which is not what we need. I want to convert this data, without lengthy programming and formulas, from the default AM/PM to reflect the song length as originally intended. What I would like to do with the resulting conversion is to sum the mm:ss output to display total time for each hour of the program.

Are there any simple solutions out there? If the solutions require a formula to convert this data, could you also supply how to apply that formula across multiple cells? Remember the data is already in the spread sheet I will not be entering the data manually. Clear as mud? Thanks for any suggestions in advance.

[Jonmo1]

You can devide the times by 60
=A1/60
And format the cells as mm:ss

These will still be considered real time values, but they will be minutes and seconds instead of hours and minutes.

[trey213]

well that was easy…I have spent 2 1/2hrs searching and never found that one…thanks! Anyway to generically apply that formula to all cells in the length column?

[trey213]

never mind that solution was easy to find on my own

[Jonmo1]

You're welcome.