Converting Epoch (Unix) Time to Date/Time Using Excel for Jmeter results

In the field of performance testing, interpreting raw data often includes converting epoch or UNIX time into a human-readable format. Excel offers a straightforward way to handle this conversion. Here's a simplified guide:

A client recently requested response time data with timestamps in an easy-to-read format. I used JMeter to conduct the testing and saved the results in a .jtl file. The steps below explain how to transform UNIX timestamps into a digestible date and time format using Excel.

Step 1: Access and Prepare the Data

• Open the JMeter .jtl log file in a text editor such as Notepad or Notepad++.
• Copy the content of the file and paste it into a new Excel spreadsheet.

Step 2: Split the Data

• Highlight all the data in the Excel sheet.
• Go to the 'Data' tab and choose 'Text to Columns.'
• In the 'Convert Text to Columns Wizard,' select 'Delimited' and click 'Next.'
• Choose 'Comma' as the delimiter and follow the wizard through to the end.

Step 3: Format the Timestamp Column

• Change the format of the timestamp column (usually column A) from 'General' to 'Number' by right-clicking the column and selecting 'Format Cells' to make the adjustment.

Step 4: Parse the Timestamp

• Insert a new column between the timestamp column and the next column.
• In the first cell of the new column (e.g., B2), enter the formula =LEFT(A2,10) & "." & RIGHT(A2,3) to separate the UNIX timestamp into seconds and milliseconds.
• Drag the formula down to apply it to the entire column.

Step 5: Convert to Date/Time Format

• Add another new column between the new column and the next one.
• In the first cell of this additional column (e.g., C2), input the formula =(((B2/60)/60)/24)+DATE(1970,1,1) to convert the parsed timestamp into a readable date/time format.
• Drag the formula down to fill in the rest of the column.

Step 6: Format the Date/Time Column

• Adjust the format of the date/time column (e.g., column C) to your preference using the 'Custom' format option.

Step 7: Review and Finalize

• Check that the date/time data in column C is now presented in a human-readable format, converted from the original UNIX timestamps.

Happy Testing !