Benches Displays

3

Exporting a playlist and displaying song …

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

I tried exporting a song list as a text file from iTunes and opened it up in Excel. The format for length of time of a song is altered. Rather than displayed as 2:30 for example it gives you a series of numbers. It looks as if it gives you the number of seconds that the song lasts. I tried writing a formula for dividing the number by 60 but then it is expressed as a decimal. Any ideas on how to preserve the minutes:seconds format? Is this an Excel question or is there another way to do this?

3 replies

Sign in or join Beachball Help to reply.

  1. 0

    b noir

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    hi Lawrence!

    Any ideas on how to preserve the minutes:seconds format? Is this an Excel question or is there another way to do this?

    🙂 would you believe that someone else has actually experimented with this?

    I once tried to build a "data picking" spreadsheet that would automatically interrogate an exported song list to automatically produce something that looked like the "text" theme of an itunes CD Jewel case print.

    (kluge kluge ... i was teaching myself excel as i went along) this sort of worked. my imported songlists would land the time column in H, so i interrogated that column with (on row 2)

    b =TIME(0,0,H2)

    ... and also applied the "m:ss" time format to the cells that that formula lived in.

    darn sure there are more elegant ways of doing it ... fiddling with the custom formats would provide a better format for tracks which are more than an hour long, for example.

    love, b

  2. 0

    Lawrence Green1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    b,

    Thank your for your reply. Could you break down your resposne for me? I have discovered that when you rxport a playlist as a textfile and open it into Excel the time is converted into seconds. I played with formulas that covert it successfully into mm:ss but not for hour or more tracks - a problem for us Deadheads who have lots of jams on our iPods. Is the =TIME(O,O,H2) a formula? What is your workaround for hour or longer tracks?

    Thanks again for responding.

  3. 0

    b noir

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    hi Lawrence!

    🙂 i warn you, this isn't going to be pretty ...

    Is the =TIME(O,O,H2) a formula?

    yes ... it's actually converting the time in seconds that you get out of the "Time" column on a songlist export to a

    b time of day.

    for example, if the seconds in H2 is 246, in a general format cell that formula returns "12:04 AM".

    to convert that to minutes and seconds, i change the format of the cell the formula is in. (using Excel Office 2003 here.)

    right-click that cell and select "Format Cells". click on the "Custom" category, scroll down to the bottom of the list to see the hours:minutes:seconds formats.

    for songs longer than an hour, maybe try

    [h]:mm:ss;@

    love, b