Excel – How to add time duration column total from Skype Call History CSV file in Excel 2010

csvdate timemicrosoft excelworksheet-function

How to add time duration column total from Skype Call History CSV file in Excel 2010?

These are my exact steps on how to set up my Excel spreadsheet.

How to import Skype Call History CVS in Excel 2010:

  1. Create a blank spreadsheet.
  2. Data >
    Get External Data > From Text >
    Import Skype Call History CSV file.
  3. Text Import Wizard > Original data type:
    Delimited > Next.
  4. Enable "Semicolon"
    checkbox under Delimiters group >
  5. Select each column in "Data
    preview" > Change all columns to
    "Text" under Column data format
  6. Select the empty last column >
    Select "Do not import column (skip)"
    under Column data format group.
  7. Finish > OK.
  8. Delete "Simplify your
    business invoices with the Skype
    Manager. Find out more at skype.biz"
    cell at the bottom.
  9. Resize columns.

Change "Duration" column to a Time format:

  1. Select all cells in the column below the "Duration" header
  2. Right click > Format Cells… > Select "Custom" > Type: "[hh]:mm:ss" > OK.

When I do a =SUM(F2:F29) in a cell below the time durations, I get a sum of 00:00:00. How do I add the times?

When I double click on a time duration cell and press Enter, Excel converts the cell from mm:ss format to hh:mm:ss format but then wrongly assumes that the minutes are hours and that the seconds are minutes. For example, 02:24 (2 minutes, 24 seconds) becomes 02:24:00 (2 hours, 24 seconds, 0 seconds). How do I fix this?

In a Skype account's Call History, you can export the data to a CSV file.


The "Export to history file" link, for example, may lead to https://secure.skype.com/account/call-history?date=2011-03&export=true, which will export the Skype Call History for March 2011.

Here is a sample Skype Call History CSV file, saved as "call_history_2011-03.csv":

"March 31, 2011 17:31";+00000000000;USA;Call;0.000;00:05;USD 0.000;
"March 31, 2011 17:30";+00000000000;USA;Call;0.000;00:11;USD 0.000;
"March 31, 2011 14:33";+00000000000;Canada;Call;0.000;03:01;USD 0.000;
"March 31, 2011 12:29";+00000000000;Canada;Call;0.000;10:59;USD 0.000;
"March 31, 2011 12:26";+00000000000;USA;Call;0.000;00:00;USD 0.000;
"March 31, 2011 11:47";+00000000000;USA;Call;0.000;00:41;USD 0.000;
"March 31, 2011 11:46";+00000000000;Canada;Call;0.000;00:19;USD 0.000;
"March 31, 2011 11:21";+00000000000;Canada;Call;0.000;00:42;USD 0.000;
"March 31, 2011 11:19";+00000000000;Canada;Call;0.000;00:38;USD 0.000;
"March 31, 2011 11:19";+00000000000;Canada;Call;0.000;00:02;USD 0.000;
"March 31, 2011 11:18";+00000000000;Canada;Call;0.000;00:02;USD 0.000;
"March 31, 2011 11:17";+00000000000;Canada;Call;0.000;00:34;USD 0.000;
"March 31, 2011 10:20";+00000000000;USA;Call;0.000;00:40;USD 0.000;
"March 31, 2011 09:51";+00000000000;USA-Toll Free;Call;0.000;17:19;USD 0.000;
"March 30, 2011 23:04";+00000000000;Canada;Call;0.000;13:43;USD 0.000;
"March 30, 2011 22:39";+00000000000;Canada;Call;0.000;00:29;USD 0.000;
"March 30, 2011 22:38";+00000000000;USA;Call;0.000;00:34;USD 0.000;
"March 30, 2011 21:55";+00000000000;Canada;Call;0.000;08:21;USD 0.000;
"March 30, 2011 18:26";+00000000000;USA;Call;0.000;00:53;USD 0.000;
"March 30, 2011 15:56";+00000000000;Canada;Call;0.000;02:18;USD 0.000;
"March 30, 2011 15:53";+00000000000;Canada;Call;0.000;00:30;USD 0.000;
"March 30, 2011 11:58";+00000000000;USA;Call;0.000;00:24;USD 0.000;
"March 30, 2011 10:01";+00000000000;USA;Call;0.000;00:11;USD 0.000;
"March 30, 2011 10:00";+00000000000;USA;Call;0.000;00:00;USD 0.000;
"March 30, 2011 10:00";+00000000000;USA;Call;0.000;00:00;USD 0.000;
"March 30, 2011 09:59";+00000000000;USA;Call;0.000;00:38;USD 0.000;
"March 30, 2011 09:56";+00000000000;USA;Call;0.000;02:24;USD 0.000;
"March 30, 2011 09:55";+00000000000;USA;Call;0.000;00:35;USD 0.000;

Simplify your business invoices with the Skype Manager. Find out more at skype.biz

Best Answer

This will be a little convoluted but I think it should work. Note, I am using Excel 2007 for this, so some steps may be a little different.

After importing your file (post step 9 above) perform the following:

  1. Insert two columns between Duration and Amount
  2. Select the data of the Duration column and go to Data > Text to Columns
  3. Choose Delimited (OK) and check Other under Delimiters, entering a colon ":" in the box. Then press Finish.
  4. The first of the two new columns should have numbers in it now.
  5. Change the Format of the second new column to General.
  6. In the second new column, in the first row under the header row, enter the formula =TIME(0,F2,G2) (where column F is the Duration column, column G is the first new column and row 2 is the first row beneath the headers)
  7. Fill the formula down for each row of data.
  8. Change the Format to display the data as desired (ex. as hh:mm:ss)
  9. Copy and paste Values and Number Formats from the second new column into the Duration column.
  10. Delete the two new columns.

This should produce output similar to thus: example

Related Question