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
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.
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.
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
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.
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.
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
thanks chris for all those informations
and don't be sorry for your interresting spaming
the formula in excel is perfect for me
I will test your post next week
Daniel
Thanks for sharing this all info.