What is the UCS Manager csv export time field format

[RedNectar]

Hi,

I exported an Events log from UCS Manager: Equipment>Rack-Mounts>Servers>Server 4>[Events] tab > Export

Note the way the time is diplayed in the GUI - the date/time clearly shows 02:07:37 UTC time on 2018-03-22.

Here is the same raw record in the csv export file - along with the header:

Affected object,Code,ID,Cause,Created at,User,Description
sys/rack-unit-4,E4196863,274515,transition,1521684457000,internal,[FSM:STAGE:SKIP]: deassert reset-bypass(FSM-STAGE:sam:dme:ComputePhysicalAssociate:MarkAdapterForReboot)

I've shown the Created at value in underlined red

How on earth does 1521684457000 convert to  02:07:37 UTC time on 2018-03-22?

More importantly, how do I get Excel to convert 1521684457000 to  02:07:37 UTC time on 2018-03-22?

When I enter then time 2018-03-22 02:07:37 in excel it becomes 43181.0886226852

Does anyone have the formula that UCS Manager uses to be able to convert the UCSM format to real times?

[Walter Dey · Accepted solution]

Hi Chris

Would this be of any help ?

https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html

or

https://www.berezniker.com/content/pages/office/excel-convert-unix-time-excel-time

Be aware that Unix time is the number of seconds since January 1, 1970 for UTC/GMT while Excel uses the number of days and fractions of the day for the local time zone.

If you convert a value such as 1322164881 using =CELL/(60*60*24)+"1/1/1970" you will get 2011-11-24 20:01:21 which is correct for UTC or the GMT time zone.

[RedNectar · Accepted solution]

[Edit: Not sure if it is cool to accept your own solution, but it stops the emails from the support community asking if I got the help I needed. Yes - I got help from Walter but needed to elobarate to provide a complete answer for anyone else who ends up here]

Thanks to Walter who pointed me in the right direction, but didn't quite make it (out by a factor of 1000).

SO to convert UCS Manager timestamps into current time (UTC), take the Created at time, divide it by 86,400,000 (=60*60*24*1000) then add to that "1/1/1970".

IN excel, if your Created at time is in cell E2, enter the following into an empty cell

=E2/(86400000)+"1/1/1970"

 The result will be an excel timestamp. Format it to a date or time field to get the result you want.

[GeorgePerkins0204]

Thanks!