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:
- Create a blank spreadsheet.
- Data > Get External Data > From Text > Import Skype Call History CSV file.
- Text Import Wizard > Original data type: Delimited > Next.
- Enable "Semicolon" checkbox under Delimiters group > Next.
- Select each column in "Data preview" > Change all columns to "Text" under Column data format group.
- Select the empty last column > Select "Do not import column (skip)" under Column data format group.
- Finish > OK.
- Delete "Simplify your business invoices with the Skype Manager. Find out more at skype.biz" cell at the bottom.
- Resize columns.
Change "Duration" column to a Time format:
- Select all cells in the column below the "Duration" header
- 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