Excel CSV issues -- the create program uses mm:ss however Excel imports them as hh:mm

Question

I use iTunes to create a CSV file and am having time issues. iTunes CSV treated track/song length as mm:ss and Excel imported them as hh:mm. I didn't notice this until I have done major editing to the created Excel file -- probably 40 or more hours. I changed the format to [m]:ss -- now new entries are correct only after typing an leading 0: which IMHO is allot of extra typing for something that should be automatic. However all the previous entries -- 32680 to be exact are now displaying the time in minutes with 0 seconds. For example if track is 3 minutes and 56 second which Excel imported it as 3 hours and 36 minutes and the above format for the cell are show as a track that is 236 minutes long. How can I fix this major issue that I have been seeing goes back several decades.

I am a Office 360 customer so I am using the latest version of Excel.

Thanks in advance,
George Worley

Answer 1

Hi @George Worley ,
How did you open the file in Excel?
I created a sample about this problem, you can use formula to help you transform the time:

=TIME(0,NUMBERVALUE(LEFT(TEXT(A1,"h:mm"),FIND(":",TEXT(A1,"h:mm"))-1)),NUMBERVALUE(RIGHT(TEXT(A1,"h:mm"),LEN(TEXT(A1,"h:mm"))-FIND(":",TEXT(A1,"h:mm")))))  

Please be a bit more precise to explain your requirement or you can upload a screenshot so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.