Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 > Next.
  5. Select each column in "Data preview" > Change all columns to "Text" under Column data format group.
  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.

https://secure.skype.com/account/call-history

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":

Date;Item;Destination;Type;Rate;Duration;Amount;
"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
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
I got #REF! when I just first copied the formulas. Here: 9) Paste > Paste Special... > "Values and number formats" under Paste group > OK. –  XP1 May 11 '11 at 1:02
    
since you accepted the answer i assume that you got it to work? –  Xantec May 11 '11 at 3:37
    
Yes.``````````` –  XP1 May 11 '11 at 3:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.