How to remove timestamps from Excel

Excel is a powerful tool for organizing and analyzing data, but sometimes your data may contain unnecessary timestamps that clutter up your spreadsheet. Whether it’s date and time information from a data export or a mistakenly entered timestamp, you may want to remove these timestamps to clean up your data. In this Tutorial, we will show you how to remove timestamps from Excel using different methods.

Here we have a random dataset, in this dataset, there is a date/time column in which there are dates and timestamps. In this tutorial, we will learn how to Remove Timestamps in excel but let’s first take a look at the Dataset above.

Method – 1 Use Custom Formatting

Step – 1 Changing the format.

• Select the column where you want to remove the timestamp.
• Click on the Home tab.
• In the Number group, click on the dropdown.

Step – 2 Formatting the cells.

• Click on the Short Date command.
• The Time Stamp will be removed.

Method – 2 DATE VALUE Formula.

Step – 1 Type the DATE VALUE Formula.

• Select the cell where you want to type the formula.
• Syntax of the formula will be

=TEXT(DATEVALUE(TEXT(Cell_Address,”dd-mm-yyyy”)),”dd-mm-yyyy”)

• In our case, the formula will be

=TEXT(DATEVALUE(TEXT(A2,”dd-mm-yyyy”)),”dd-mm-yyyy”)

Step – 2 Finding values of the rest of the cells.

• Select the cell containing the formula.
• Drag the cells from the bottom right to the rest of the cells in the column.
• The formula will be applied automatically.

Breakdown of the formula

=TEXT(DATEVALUE(TEXT(A2,”dd-mm-yyyy”)),”dd-mm-yyyy”)

This function converts a date stored as text in cell A2 from the format “dd-mm-yyyy” (day-month-year) to the same format but as a proper Excel date value.

Here’s how it works:

The innermost function, TEXT(A2, “dd-mm-yyyy”), takes the text string in cell A2 and converts it to a new text string in the desired date format “dd-mm-yyyy”.

The DATEVALUE function then converts this new text string into an Excel date value.

Finally, the outermost TEXT function converts this Excel date value back into a text string in the “dd-mm-yyyy” format.

The result is that you have a properly formatted Excel date value that you can use for calculations and other operations that require dates, rather than a simple text string.