Question & Answer
Question
How to convert date values in CSV (comma separated values) exported reports from RAW format to a human readable format ?
Cause
When exporting Tivoli Storage Manager - Data Protection for VMware GUI reports with version 7.1.1 or higher in CSV format, the date values are in RAW format.
This means that the date values are expressed in milliseconds and in Unix time (a.k.a. POSIX time or Epoch time) which is the time elapsed since 00:00:00 Coordinated Universal Time (UTC) on 1 January 1970.
Answer
To translate the dates using spreadsheet tools, the following needs to be done :
To get the UTC time insert in the spreadsheet cell :
=((Cell_id/1000)/86400+25569)
Then edit the cell properties to tell it is a date format and you will get the UTC time in human readable format.
To adjust it for another timezone, add or subtract the number of hours (in seconds) relative to UTC :
=(((Cell_id/1000)+/-timezone difference)/86400+25569)
For example to subtract 2 hours, the formula is :
=(((C4/1000)-(2*3600))/86400+25569)
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21691127