How to Remove Milliseconds from Timestamps in Google Sheets

Removing milliseconds from timestamps in Google Sheets is an essential technique that can help troubleshoot formula errors. Whether you’re dealing with static timestamps or matching criteria in the timestamp column, it’s crucial to understand how to remove milliseconds for accurate results.

Truncating Milliseconds from Timestamps

If you want to truncate the milliseconds from a timestamp in Google Sheets, follow this simple approach:

1. Assume the timestamp is in cell A2.
2. Use the formula =A2-QUERY(A2, "Select millisecond(A) label millisecond(A)''", 0)/86400000 in any other cell.

For example, if the timestamp is 13/08/2023 08:47:08.704, the formula will return 13/08/2023 08:47:08. By dividing the extracted milliseconds value by 86400000 (the number of milliseconds in a day) and subtracting it from the original timestamp, you can effectively remove the milliseconds.

Removing Milliseconds from a List using Array Formulas

You can also remove milliseconds from a list of timestamps in Google Sheets using array formulas. Here’s the formula to achieve that:

=ArrayFormula( IF(A2:A="", , A2:A-QUERY(A2:A, "Select millisecond(A) label millisecond(A)''", 0)/86400000) )

The array formula applies the subtraction, division, and an IF logical test to the entire column. It ensures that the formula returns a blank for rows without a timestamp value in column A.

Adjusting Timestamps to the Nearest Second

If you want to remove milliseconds but round the seconds part of the timestamp, you can use the following formulas:

• =DATEVALUE(A2) + TIMEVALUE(TEXT(A2, "HH:mm:ss"))
• =DATEVALUE(A2)+TIME(HOUR(A2), MINUTE(A2), SECOND(A2))

In both formulas, the DATEVALUE function returns the date value, while the TIME/TIMEVALUE functions retrieve the time component. By either converting the time string or using individual time components, you can effectively adjust the timestamps to the nearest second.

Using Array Formulas for Adjusted Timestamps

You can apply the adjusted timestamp formulas to a range of timestamps using array formulas as well. Here are the examples:

• =ArrayFormula(IFERROR(DATEVALUE(A2:A) + TIMEVALUE(TEXT(A2:A, "HH:mm:ss"))))
• =ArrayFormula(IFERROR(DATEVALUE(A2:A)+TIME(HOUR(A2:A), MINUTE(A2:A), SECOND(A2:A))))

The ArrayFormula ensures that the functions are applied to the entire range of timestamps. Additionally, the IFERROR function handles errors returned by the DATEVALUE function in blank cells, converting them to blanks.

Conclusion

In this article, we explored simple solutions for removing milliseconds from timestamps in Google Sheets. Whether you choose to truncate the timestamps or adjust them to the nearest second, these techniques will help you ensure accurate data analysis and troubleshooting.

For more resources related to working with milliseconds in Google Sheets, visit Crawlan.com.