VMware Cloud Community
Daniel1966
Contributor
Contributor
Jump to solution

reports time conversion

hello

when we export a usage meter report the time column date and time is converted to a 13 digit number

is it possible to have the conversion algorithm to be able to reconvert this value to date and time?

Regards

Daniel

Reply
0 Kudos
1 Solution

Accepted Solutions
metalgear32
VMware Employee
VMware Employee
Jump to solution

Hi Daniel,

The timestamp format you're seeing is an epoch timestamp (more specifically a millisecond epoch timestamp). You can convert the timestamps using something like http://www.epochconverter.com, though this probably doesn't accomplish what you're looking for. Are you wanting the exported reports to show the timestamp as HH:MM:SS MM/DD/YY? If so you may need to submit this as a feature request, but I will do some tinkering in my lab to see if I can't get it to export in that format.

EDIT: A few quick searches has turned up a few posts on the MS forums discussing converting Epoch timestamps in Excel. This link seems to include a formula which will convert the epoch timestamp to an excel readable format, which can then be converted to a human readable format by setting the cell to a Time/Date format.

http://www.mrexcel.com/forum/excel-questions/49217-converting-normal-time-epoch-time-format-using-ex...

I haven't tested this yet, since the posters in the forum don't specify whether or not the timestamps they're dealing with are in seconds or milliseconds, so it may require further conversion for this to work if they're dealing in seconds with the formula provided.

EDIT Again: I took the formula they suggested in the link above and it appears to work only on timestamps that are provided in seconds. Since the timestamp we provide is in milliseconds you will need to devide the time value by 1000 resulting in the following formula:

=((A1/1000)/86400)+25569

Where A1 is the cell containing the timestamp. I have provided a screenshot of this formlua in action below. Let me know if you have any questions. Sorry if all of my editing is spamming your e-mail.

Screen Shot 2012-11-29 at 5.51.17 PM.png

Final Edit (I promise): I pulled a report from Usage Meter into excel, to see if it was possible to convert the entire column, and so far the best solution I have been able to find is as follows:

- Insert a new column between #Time and Host DNS Name (I called it "Converted Time")

- Select the row below the heading and input your formula (B11 in my case)

- Formula "=((A11/1000)/86400)+25569" (with the additional info at the top of the rerport the time values started at A11)

- Press enter to insert the formula

- Select the cell containing the formula

- Double click the box in the lower right corner of the cell, this will apply the formula to all subsequent rows

- Select the column, and right click to open the "Format Cells" menu

- Set the colum to "Time" and select the desired format

I hope this information has been helpful!

-- Chris

Technical Support Engineer | VCP4

VMware Global Support Services

View solution in original post

Reply
0 Kudos
3 Replies
metalgear32
VMware Employee
VMware Employee
Jump to solution

Hi Daniel,

The timestamp format you're seeing is an epoch timestamp (more specifically a millisecond epoch timestamp). You can convert the timestamps using something like http://www.epochconverter.com, though this probably doesn't accomplish what you're looking for. Are you wanting the exported reports to show the timestamp as HH:MM:SS MM/DD/YY? If so you may need to submit this as a feature request, but I will do some tinkering in my lab to see if I can't get it to export in that format.

EDIT: A few quick searches has turned up a few posts on the MS forums discussing converting Epoch timestamps in Excel. This link seems to include a formula which will convert the epoch timestamp to an excel readable format, which can then be converted to a human readable format by setting the cell to a Time/Date format.

http://www.mrexcel.com/forum/excel-questions/49217-converting-normal-time-epoch-time-format-using-ex...

I haven't tested this yet, since the posters in the forum don't specify whether or not the timestamps they're dealing with are in seconds or milliseconds, so it may require further conversion for this to work if they're dealing in seconds with the formula provided.

EDIT Again: I took the formula they suggested in the link above and it appears to work only on timestamps that are provided in seconds. Since the timestamp we provide is in milliseconds you will need to devide the time value by 1000 resulting in the following formula:

=((A1/1000)/86400)+25569

Where A1 is the cell containing the timestamp. I have provided a screenshot of this formlua in action below. Let me know if you have any questions. Sorry if all of my editing is spamming your e-mail.

Screen Shot 2012-11-29 at 5.51.17 PM.png

Final Edit (I promise): I pulled a report from Usage Meter into excel, to see if it was possible to convert the entire column, and so far the best solution I have been able to find is as follows:

- Insert a new column between #Time and Host DNS Name (I called it "Converted Time")

- Select the row below the heading and input your formula (B11 in my case)

- Formula "=((A11/1000)/86400)+25569" (with the additional info at the top of the rerport the time values started at A11)

- Press enter to insert the formula

- Select the cell containing the formula

- Double click the box in the lower right corner of the cell, this will apply the formula to all subsequent rows

- Select the column, and right click to open the "Format Cells" menu

- Set the colum to "Time" and select the desired format

I hope this information has been helpful!

-- Chris

Technical Support Engineer | VCP4

VMware Global Support Services

Reply
0 Kudos
Daniel1966
Contributor
Contributor
Jump to solution

thanks chris for all those informations

and don't be sorry for your interresting spaming Smiley Happy

the formula in excel is perfect for me

I will test your post next week

Daniel

Reply
0 Kudos
rsneha
Contributor
Contributor
Jump to solution

Thanks for sharing this all info.

Reply
0 Kudos