Exported data or audit log entries - how to format date/time correctly in Excel

This article will answer the following quesitons:

- Why don't I see time or date entries on exported XML data from graphs ?

- Why don't I see time or date entries (timestamps) on exported CSV audit log entries ?

- What format are the 'snapshot_time' entries in exported CSV data?

-How do I convert 'snapshot_time' into actual date or time in Excel ?

 

Data exported from VMTurbo contains timestamps in the common 'Unix time' format  (also know as  ctime, Epoch time)  - this is actually the number of milliseconds since 1/1/1970.

When you open the XML or CSV data you have exported into Excel, the field "time" (XML) or the field "snapshot_time" (CSV) contains the Unix Time stamp.

 

To convert from Unix Time to 'human readable time' there are 3 steps:

- Create a new column to hold the 'human readable time'

- Apply a formula to convert from Unix Time to Human Readable Time

- Apply a format to display the date/time correctly

 

1. Create a new column to hold 'human readable time'

This is an Excel function. Choose 'Insert Column', where you wish the column to appear. In the examples here, we are using a CSV file, and have insert the column as the first Column (Column 'A')

2. Apply a formula to convert the time values

The formula removes the msec from the value (as these are not usually of interest), and converts from Unix Time.

=B2/1000/24/60/60+"1/1/1970"

For the formula above, this would be placed on Row 2, Column A (assuming, we have column header titles, and that the new column is at Column A containing this formula) Note that 'B2' refers to Cell 2, Column B, where our first "Unix Time' data cell is. You should of course make 'B2' refer to the correct source data for your spreadsheet.

After this formula is created, you should then copy it to the rest of the rows.

You should then see a long number as the result of this formula.

 

3. Format the cell with a date/time format

Most usually, you will choose a 'custom' format to give seconds, as well as date and HH:MM.

 

For my example below I have chosen dd/mm/yyyy hh:mm:ss as my desired date format (check Excel documentation for explanations of the available codes).

Format_Cells.png

 

Once this format is applied, you should be able to see the timestamps successfully.

Have more questions? Submit a request

Comments