03-24-2018 12:54 PM - edited 03-01-2019 01:28 PM
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?
Solved! Go to Solution.
03-25-2018 02:51 AM - edited 03-25-2018 02:58 AM
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.
03-26-2018 03:59 AM - edited 04-01-2018 01:42 PM
[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.
03-25-2018 02:51 AM - edited 03-25-2018 02:58 AM
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.
03-26-2018 03:59 AM - edited 04-01-2018 01:42 PM
[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.
08-10-2022 07:52 AM
Thanks!
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: