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:

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

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:

Insert two columns between Duration and Amount
Select the data of the Duration column and go to Data > Text to Columns
Choose Delimited (OK) and check Other under Delimiters, entering a colon ":" in the box. Then press Finish.
The first of the two new columns should have numbers in it now.
Change the Format of the second new column to General.
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)
Fill the formula down for each row of data.
Change the Format to display the data as desired (ex. as hh:mm:ss)
Copy and paste Values and Number Formats from the second new column into the Duration column.
Delete the two new columns.

This should produce output similar to thus: example

Related Solutions

Excel – How to change the hh:mm:ss format in spreadsheets

I've written a VBA function that will add your minutes and seconds:

Function addTime(rng As Range) As String
    Dim timeArray As Variant
    Dim sumSec As Integer
    Dim sumMin As Integer
    Dim i As Range

    Dim secStr As String
    Dim minStr As String
    Dim hrStr As String

    sumMin = 0
    sumSec = 0

For Each i In rng
        On Error GoTo Continue
        If i.Value <> "" Then
            timeArray = Split(i.Value, ":")
            sumMin = sumMin + timeArray(0)
            sumSec = sumSec + timeArray(1)
        End If
Continue:
        On Error GoTo 0
    Next i

    secStr = Modulo(sumSec, 60)
    If (secStr < 10) Then
        secStr = "0" & secStr
    End If

    minStr = Modulo((sumMin + sumSec \ 60), 60)
    If (minStr < 10) Then
        minStr = "0" & minStr
    End If

    hrStr = sumMin \ 60

    addTime = hrStr & ":" & minStr & ":" & secStr    
End Function

Function Modulo(a, b)
    Modulo = a - (b * (a \ b))
End Function

Put the two functions in a module.

To use my function, first you need to get your data into Excel as-is, i.e. not let Excel change the data into its date-time format. So:

First, click the column(s) you want to paste your data in
Convert the Format to "Text"
Paste your data
Type =addTime(A1:A10) (using "A1:A10" as an example of 10 data points)

Excel – does Excel treat some dates differently than others

You need to straighten out your regional settings and your date formats and align them with the month and day order of the dates in the CSV. They don't seem to gel at the moment.

41282.00003 is 8 January 2013 and not 1 August 2013.

Excel will try to interpret a date according to your computer's regional settings. If the regional settings are DMY and the date to be interpreted is 07/31/2013, the DMY order will not work and Excel will interpret the data as text. That is what you see. Text that looks like a date/time value. Try to format that apparent date/time value differently. You will see that you cannot, because it is text.

But if the next row of data has 08/01/2013, this fits very well into the regional setting's DMY scheme and it will be returned as 8-Jan-2013. You can change the cell's format to custom format

dd/mm/yyyy hh:mm:ss.000

and it will show as 08/01/2013 00:00:02.863

enter image description here

The value in cell A1 is the text value, not a real date time. The cell format is "General" and no amount of number formatting will change its appearance.

The value in cell A2 is a real date/time value, formatted with the above mentioned custom format.

When you import dates, take extra double care to check the order of day and month in the imported data. When you use the import wizard, you can specify what order the source data is in and all dates will be imported consistently.

Let's take a closer look at the second (real) date. 08/01/2013 00:00:02.863 formatted with "General" displays as 41282.00003 and formatted with a proper date shows as 8-Jan-2013. Fine.

If you increase the decimals for the General format, you will find that the actual underlying number is 41282.0000331366. This number has 15 numeric digits.

Formatted as a date, you can edit it and change the day from 8 to 7. The result will show in "General" Format as 41281.00003, but if you increase the number of displayed digits, you will see that the number is 41281.00003472220

Huh?

How come? We only subtracted one day, so only the number before the decimal point should change.

Well, Excel has a built-in accuracy of only 15 digits for any number. Numbers with more digits will be rounded or the last digits will be replaced with zeros. Also, there is a well-known bug in Excel that affects the accuracy of numbers where the 15 digit limit is reached.

I think this is one example where the bug rears its ugly head.

When the date portion of our date/time value is changed, it will also cause a re-assessment of the decimals, which will lead to some rounding and inconsistent behaviour after the 4th decimal. Therefore, the actual second and millisecond data will be off.

See if this screenshot helps clarify:

enter image description here

The values in columns B to D all reference column A. The only difference between A1 and A2 is a manual change of the date from 08/01 to 07/01 (where the 01 is January, according to the regional settings of DMY).

The "General" format shows both values with a x.0003 decimal value. Extending the decimals shows that there is quite a difference in the decimals following the 4th decimal.

Since the desired end result is a value that shows seconds and milliseconds, the decimals after the 4th decimal really make a difference, and when the value is formatted with a custom format that shows seconds and milliseconds, that difference shows (in column A).

Also, note the three cells with the numbers consisting of just 15, 16 and 17 digits of 1, and how Excel simply replaces any digit after the 15th with a zero, because it cannot display a higher accuracy.

Best Answer

Related Solutions

Excel – How to change the hh:mm:ss format in spreadsheets

Excel – does Excel treat some dates differently than others

Related Question