This question came up recently in relation to converting all of the recordings on an account to MP4 using the conversion service provided by eSyncTraining. The administrator of the Connect account wanted to see all of the recordings and I added the thought to see the overall hours of recordings on the account as it seems valuable as well.
Note – I am not an Excel expert so I think some of you may have shorter ways to slice-and-dice the information in the spreadsheet, but the technique below worked for me.
Get the Raw Report of all of your Recordings
The easy part. Login to your Connect account as an Administrator and navigate to ‘Administration’>’Account’>’Reports’>’View Downloadable Reports’ (Figure 1 below). On the next page scroll down to the ‘Assets’ section and select ‘Download Report Data’. Save the Excel file to your computer.
Figure 1
Edit the data in Excel to make it a little more useful
The original report contains all of the assets in your account so the first thing we need to do is to sort it and delete unnecessary data so that we only see the recordings. Draw your mouse over all of the headers to the cells (A,B,C…etc…) to select them all as shown in Figure 2 below.
Figure 2
Now we want to sort the cells to group the recordings. With the cells selected, click on the ‘Data’ menu and ‘Sort’. In the dialogue box, select sort by ‘Column’>’Icon’ and ‘Order’>’A to Z’ (Figure 3). Click ‘OK’.
Figure 3
You will now see that in your spreadsheet, the ‘icon’ field has ‘archive’ sorted to the top of the list. The ‘archive’ content is actually your recordings (Figure 4).
Figure 4
You can scroll down the list and delete any other cells from the spreadsheet as we only want the recordings (‘archive’ content listings) in this case (select the other content cells and then ‘right-click’>’Delete’). You may also want to resize the cells so that you can read the descriptions more easily.
You can use this list as it stands to see the full list of recordings in your account. If you want to get the overall duration of recordings on your account then read on, but fair warning that it is a little bit of an Excel nerd-fest.
A little ‘housekeeping’ first
We are going to find the duration of each recording and add them up. If there are any ‘orphans’ or null duration recordings in the list it will mess up the calculation and formulas. Find any recordings with no start of end information and delete these rows from the list (see Figure 5).
Figure 5
Finding the Duration of each of the Recordings (this is how I did this anyway)
The columns ‘date-created’ and ‘date-end’ can in theory be subtracted to give us the duration of the recording. Unfortunately the fields are in a relatively unhelpful format:
| date-created | date-end |
| 2012-06-11 13:07:13 | 2012-06-11 13:42:43 |
We want the ‘time-in’ and ‘time-out’ numbers at the end of the cells. We can extract that piece of information using the ‘RIGHT’ function in Excel.
Add 3 new columns to the right of the ‘date-end’ column (‘right-click’ on the header of the column and ‘Insert’). Title the columns as shown below:
| Extract recording start time | Extract recording end time | Recording Duration |
In the first ‘Extract recording start time’ cell insert the formula ‘=RIGHT(F3,8)’. Where ‘F3’ is the ‘date-created’ cell and the number ‘8’ represents capturing the last 8 characters from the right of the cell, which is the hours, minutes and seconds of the start-time for the recording (Figure 6 below).
Figure 6
Copy the cell above to the cell to the right (‘Extract recording end time’) and it will automatically point to the ‘date-end’ cell on the left. If you look at Figure 7 below you can see that the hh:mm:ss data has been extracted into the new cells.
Figure 7
Now to find the duration of the recording. The simple formula is to subtract the recording start time from the recording end time. You can see the formula in Figure 8 below. In this case it is ‘=SUM(I2–H2)’.
Figure 8
Now when you use the field above you may get a seemingly unhelpful number as you can see if Figure 9 below. This is the correct calculation, but it needs to be shown in hours, minutes and seconds (‘hh:mm:ss’). The example below in Figure 9 shows that the formatting of the cells is ‘General’ so the time is shown as a decimal. We will change this.
Figure 9
Select the cell with the recording duration in it and then ‘right-click’. In the next menu select ‘Format Cells…’ as shown in Figure 10 below. In the next window you want to select the ‘Number’ tab/option, then select ‘Custom’ and in the ‘Type:’ field add ‘hh:mm:ss’ (without the quote marks), click ‘OK’ and you will see your cell reflect the correct duration of the recording (Figures 11 and 12 below).
Figure 10
Figure 11
Figure 12
Almost there!
Copy the three cells you have created to determine the recording duration down to the rest of your list and the calculation will be repeated down the whole list (Figure 13).
Figure 13
Now add all of the ‘Recording Duration’ column together to get the total hours of recordings on your account. At the bottom of the column, use the formula ‘=SUM(cell-range)’ and drag to include all of the cells in the column (Figure 14 below). Done? Nope! If you look at the total in ‘hh:mm:ss’ in my example in Figure 14, it says I only have 10 or so hours of recordings and that cannot be right?! This is again a trick with the cell formatting.
Note – if you see an error in the totals field, chances are that you have not deleted a null/orphan recording field as shown in Figure 5 above. Delete the offending rows and it should work.
Figure 14
Select the cell that has the total of all of the recording durations and ‘right-click’, again select ‘Format Cells…’ and edit the ‘Type’ box to show ‘[hh]:mm:ss’ (no quote marks). You care adding square brackets around the ‘hh’ text to allow it to show the actual number of hours vs. a true ‘clock-time’. You can see my total hour in Figure 16 below (1,114 hours of recordings).
Figure 15
Figure 16
Done!
Apology to Excel experts – I am sure you have a better way to do this in less steps, this process worked for me…