3 Replies Latest reply on Feb 28, 2019 2:00 AM by rsneha

    reports time conversion

    Daniel1966 Novice

      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

        • 1. Re: reports time conversion
          metalgear32 Novice
          VMware Employees

          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-excel.html

           

          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

          • 2. Re: reports time conversion
            Daniel1966 Novice

            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

            • 3. Re: reports time conversion
              rsneha Lurker

              Thanks for sharing this all info.