CSV export from the Performance Views uses Milliseconds for all time/duration values.  Many have asked for an easy way to see this data in a more standard Excel format that mimics the views - HH:MM:SS.   I've created a simple Macro in Excel which quickly and easily converts these columns from MS to HH:MM:SS that I use all the time when looking at the exports – it's simple and easy. 

Here's the instructions to install the macro in Excel.  Note:  the Macro persists in Excel so it only needs to be installed once. 

1. Rename/Save the file (ConvertMS.xlam) to your hard drive (make sure to remove the .zip extension)
2. Open Excel
3. If you don't see developer ribbon then you will want to add it:  File>Options>Customize Ribbon>Select Checkbox next to Developer on Right Panel
4. Go to Developer Ribbon and select Excel Add-ins
5. Browse and select the file you saved above (ConvertMS.xlam)
6. Add-in should be installed and looks like a circle (see screenshot below - install macro.png)
7. Open any of your CSV Files in Excel and select all column(s) and/or cell(s) that you want to convert from MS to HH:MM:SS and click the macro circle icon (Note:  Macro will ignore the column header text fields so you can select the entire column(s))

 

Note:  the Macro persists in Excel so it only needs to be installed once. 

 

In case you have issues downloading the macro file or installing it here's the code that I used to create the macro (P.S. It's been a long time since I was a developer so I'm sure some of you could build something much cooler) :)

 

Sub ConversionMacro()

'

    Dim cel As Range

    Dim selectedRange As Range

   

    Set selectedRange = Application.Selection

 

    For Each cel In selectedRange.Cells

   

    If Not IsEmpty(cel.Value) And cel.Value > 0 And IsNumeric(cel.Value) Then

     

        cel.FormulaR1C1 = "=" & Val(cel.Value) & "/86400000"

        cel.NumberFormat = "[h]:mm:ss"

       

   End If

  

    Next cel

 

End Sub

 

------------------------------
Darlene Oordt
Sr Director, Product Management
Genesys - Employees
------------------------------

If you're one a Mac, step three is a bit different:
For Mac: Select Excel > Preferences > Ribbon & Toolbar. Under Customize the Ribbon, select Main Tabs and then check Developer. Click Save and then close Excel Preferences.

------------------------------
Jordan Robinson-Rucker
PureCloud Technical Writer
Genesys - Employees
------------------------------

Original Message:
Sent: 10-18-2018 21:16
From: Darlene Oordt
Subject: Macro to Convert Milliseconds (MS) to HH:MM:SS for Duration Columns in CSV View Exports

CSV export from the Performance Views uses Milliseconds for all time/duration values.  Many have asked for an easy way to see this data in a more standard Excel format that mimics the views - HH:MM:SS.   I've created a simple Macro in Excel which quickly and easily converts these columns from MS to HH:MM:SS that I use all the time when looking at the exports – it's simple and easy. 

Here's the instructions to install the macro in Excel.  Note:  the Macro persists in Excel so it only needs to be installed once. 

1. Rename/Save the file (ConvertMS.xlam) to your hard drive (make sure to remove the .zip extension)
2. Open Excel
3. If you don't see developer ribbon then you will want to add it:  File>Options>Customize Ribbon>Select Checkbox next to Developer on Right Panel
4. Go to Developer Ribbon and select Excel Add-ins
5. Browse and select the file you saved above (ConvertMS.xlam)
6. Add-in should be installed and looks like a circle (see screenshot below - install macro.png)
7. Open any of your CSV Files in Excel and select all column(s) and/or cell(s) that you want to convert from MS to HH:MM:SS and click the macro circle icon (Note:  Macro will ignore the column header text fields so you can select the entire column(s))

 

Note:  the Macro persists in Excel so it only needs to be installed once. 

 

In case you have issues downloading the macro file or installing it here's the code that I used to create the macro (P.S. It's been a long time since I was a developer so I'm sure some of you could build something much cooler) :)

 

Sub ConversionMacro()

'

    Dim cel As Range

    Dim selectedRange As Range

   

    Set selectedRange = Application.Selection

 

    For Each cel In selectedRange.Cells

   

    If Not IsEmpty(cel.Value) And cel.Value > 0 And IsNumeric(cel.Value) Then

     

        cel.FormulaR1C1 = "=" & Val(cel.Value) & "/86400000"

        cel.NumberFormat = "[h]:mm:ss"

       

   End If

  

    Next cel

 

End Sub

 

------------------------------
Darlene Oordt
Sr Director, Product Management
Genesys - Employees
------------------------------