I got the idea for this tutorial from MiTapes, a music player app for the iPhone that allows you to create mixtapes, just as you did on cassette tapes back in the old days. With MiTapes, you select a tape duration (30, 45 or 60 minutes per side) and select which tracks you’d like to store on the tape. When the side is "full" a message is displayed. I decided to try and replicate this functionality in Excel.
If you want to "play along" with this tutorial, you can download the demo file here.
As you can see from the screenshot below, the spreadsheet contains a selection of music tracks. Each track has a duration (MM:SS).
You select a "Tape Type" in G7 (C60, C90 or C120) – to make it easier I have added a drop-down using data validaton.
G8 contains a VLOOKUP formula that calculates the number of minutes per side (using F3:G5 as the lookup table): =VLOOKUP(G7,F3:G5,2,FALSE)
You select the tracks that you want to put onto side A and side B of the cassette tape by entering the letter A or B into each cell in column D (to make it easier I have added a drop-down to each cell using data validation).
The SUMIF formulas in G9 and G10 display the total duration for each side by adding up the values in column C but only where column D contains A or B respectively:
- =SUMIF(D2:D39,"A",C2:C39)
- =SUMIF(D2:D39,"B",C2:C39)
The cells have conditional formatting applied so that the value is displayed in red if the minute element of the total duration for a particular tape side (G9 or G10) is greater than or equal to the minutes per side for the selected tape type (G8). The conditional formatting rule is a formula: =MINUTE(G10)>$G$8 and =MINUTE(G9)>$G$8
Cells H9 and H10 contain IF functions that display a message "Side Full" if the minute element of the total duration for a particular tape side (G9 or G10) is greater than or equal to the minutes per side for the selected tape type (G8).
- =IF(MINUTE(G9)>=G8,"Side Full","")
- =IF(MINUTE(G10)>=G8,"Tape Full","")
Just a note about the track information – rather than entering these manually I copied some tracks from a demo iTunes playlist. I opened iTunes, selected the playlist, made sure that the appropriate columns were displayed in the playlist, highlighted the tracks (clicked the first one, held down SHIFT and clicked the last one), selected Edit > Copy, switched to the Excel file, positioned my cursor in A2 and selected Paste.
After pasting the track details into Excel, I found a problem with the durations in column C. Taking the first track as an example, it displayed 03:02 in the cell, which appears to be correct (3 minutes, 2 seconds). However, what was displayed in the formula bar was 03:02:00, which Excel interprets as 3 hours, 2 minutes, 0 seconds.
To convert HH:MM:SS to MM:SS:
- I entered the following formula into a blank cell (E2) =C2/60
- I then copied the formula down for all the other durations
- I selected all the cells containing the formulas and issued the Copy command
- I used Paste > Values to replace the "incorrect" durations in column C with the correct durations from column E
- I selected the cells in column C and applied a custom format of MM:SS
- Finally, I deleted the formulas from column E
ps: if you’d like to see a short video review of MiTapes, check out my personal blog