A couple of days ago, I wondered what percentage of my life I listen to music. Fortunately, I figured out exactly how to calculate it. I have been using iTunes since I got my first iPod for my 18th birthday in 2005. Three computers and four iPods later, I have managed to preserve my playcounts for the last eight years. A little bit of Excel handiwork revealed that I have spent about 450 days listening to that music, or about an eighth of all my time. That’s about a quarter of all my waking hours!
Want to calculate your time? Here is the process in ten steps. If you have even the slightest experience with formulas in Excel, this will be painless:
1) Open your iTunes and go to your songs tab. Make sure iTunes is showing a column for Time and Plays. (If it isn’t, you can fix this by right clicking on the columns and checking the appropriate categories.)
2) Press CTRL+A to highlight all the songs and then press CTRL+C to copy them.
3) Open Excel and press CTRL+V to paste all the information. Surprisingly, the information nicely conforms to the cells.
4) iTunes gives a lot of information superfluous to our purposes. Ignore everything but the column for Time and Plays (columns B and I for me).
5) Create a new column by multiplying the time column by 24.
6) Highlight your new column (column K for me) and use the drop down formatting menu to convert it to a number.
You might be thinking “why on earth are we doing this?” for the last two steps. Good question. This is part is fancy, so bear with me. Excel treats the time column as a time of day rather than a number of minutes and seconds. For example, it thinks cell B1 refers to 5:09 AM, not 5 minutes, 9 seconds. Excel stores this information as a proportion of a 24 hour day. As such, it thinks of 5:09 AM as approximately 0.21458. Multiplying by 24 and converting it to a number gives us the number of minutes each song is.
7) Create a new column by multiplying the previous new column by the Plays column. (For me, this is multiplying column I by column K.) The result is the total number of minutes you have listened to each song.
This in itself is interesting, and you can use Excel to sort the songs by the most time you have spent listening to them. This list can be quite different from your most played songs, as a 5 minute song counts for twice as much as a 2 minute 30 second song.
8) Use the SUM function to sum the column you created in step 7. This is the total number of minutes you have spent listening to iTunes.
9) Divide by 60 to get the number of hours you have spent listening to iTunes.
10) Divide step 9 by 24 to get the number of days you have spent listening to iTunes.
Can anyone top 450 days? Post your times in the comments below…