Most of the performance testing tools produce the raw results that contain epoch or UNIX time which might need to be converted into human readable date and time format and this conversion can be easily done through Excel. But how? Let me demonstrate you in the simple way:
I came across a requirement where the client needs the response time of each request along with the timestamp in a human-readable format. I ran the test with JMeter and captured the logs in the .jtl file. So, I copied the content of the .jtl file into Excel and used the below logic to convert the UNIX timestamp into a human-readable date/time format.
Conversion of Epoch (Unix) Time to Date/Time through Excel
Pre-requisite: JMeter test execution log file in .jtl format.
Steps: Open the .jtl file in Notepad or Notepad++.
Now, copy the content into MS Excel.
Select the content of the excel, click on the ‘Data’ tab and then choose the ‘Text to Columns’ option
Select the ‘Delimited’ option under ‘Convert Text to Columns Wizard’
Click ‘Next’ and select ‘Comma’
Click ‘Next’ and ‘Finish’
Now, change the format of column A from ‘General’ to ‘Number’, by selecting the column’s content then right-click and select the option ‘Format Cell…’
Now, add a new column between A and B
Add the below formula in Cell B2
=LEFT(A2,10) & "." & RIGHT(A2,3)You will get the value like the below screenshot:
Drag the formula till the last value
Add another new column between B and C
Add the below formula in Cell C2
=(((B2/60)/60)/24)+DATE(1970,1,1)You will get the value in column C similar to the below screenshot:
Drag the formula of C2 till the last value.
Now format the column C in the required format using the ‘Custom’ format option
And, the final output is:
So, finally, you can get the human-readable Date/Time in Column C.
You may be interested: