Epoch (Unix) Time to Date/Time through Excel

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++.

JMeter JTL file screenshot

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:

Epoch (Unix) Time to Date/Time into Excel

Drag the formula till the last value

Epoch (Unix) Time to Date/Time into Excel

Add another new column between B and C

Epoch (Unix) Time to Date/Time into Excel Formula 1

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:

Epoch (Unix) Time to Date/Time into Excel Formula 2

Drag the formula of C2 till the last value.

Now format the column C in the required format using the ‘Custom’ format option

Epoch (Unix) Time to Date/Time into Excel - Format Cell

And, the final output is:

Epoch (Unix) Time to Date/Time into Excel - Conversion

So, finally, you can get the human-readable Date/Time in Column C.


You may be interested:


Leave a Comment