Formatting Issues

How to Remove Time from Excel Dates Using Text to Columns

Khadija Khan Oct 19, 2026 868 views

Question details

The user needs to remove the underlying time component from date entries in a spreadsheet, as simply changing the display format does not affect the actual data value.

How to Remove Time from Excel Dates Using Text to Columns

Product: Excel
Device & OS: not provided
Scenario: Exporting or preparing spreadsheet data where another application requires strictly date values without time components.
Observed behavior: Changing the cell format to mm/dd/yyyy only hides the time visually. The underlying time value persists and causes data rejection in other applications.

Before you start

Ensure you have selected the correct column containing the date and time values. It is also good practice to make sure you have saved your current progress before making bulk data transformations.

Solution 1Recommended

Use Text to Columns to Remove Time Values

The most efficient method to force Excel to strip time data from a date column without editing each cell manually.

Changing a cell's display format does not alter the actual data stored in the spreadsheet. The Text to Columns feature forces the application to re-evaluate the data and strip out the time component, leaving behind a pure date value.

Select the date column

Highlight the entire column containing the dates with the time values that you want to remove.

Open Text to Columns

Go to the Data tab on the top ribbon and click on the 'Text to Columns' button.

Choose Delimited

In the Text to Columns wizard, select 'Delimited' as the original data type and click 'Next' twice to skip to step 3 of the wizard.

Set Column Data Format

Under the 'Column data format' section, select the 'Date' radio button and choose 'MDY' (Month, Day, Year) from the adjacent drop-down list.

Finish Conversion

Click 'Finish'. The values in your selected column will be instantly converted to pure dates without the underlying time component.

Use Text to Columns to Remove Time Values

Verification Step: You can verify the time is completely gone by clicking on any converted cell and looking at the formula bar at the top; it should display only the date.

Free Spreadsheet Tool

Easily Manage Date Formats in WPS Spreadsheet

WPS Spreadsheet provides robust data management tools, including the Text to Columns wizard, to quickly clean up date and time formats. It is lightweight, perfectly suited for bulk data formatting, and completely free to use.

1. Select Your Data: Highlight the column containing the dates you want to format in your WPS Spreadsheet workspace.
2. Launch Text to Columns: Navigate to the Data tab on the ribbon and click on the 'Text to Columns' icon.
3. Apply Date Format: Follow the prompts, select Delimited, choose Date (MDY) in the final step, and click Finish to clean your data.

Easily strip time components from dates using the built-in Text to Columns featureFully compatible with Microsoft Excel (.xlsx, .xls, and .csv) formatsLightweight software ensuring fast data processing even with large datasetsFamiliar ribbon interface that requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does changing the cell format to mm/dd/yyyy not remove the time?

Cell formatting only changes how the data is visually displayed on your screen. The underlying value stored in the system still includes the time component unless it is fundamentally altered using a tool like Text to Columns or a mathematical formula.

Can I use formulas instead of Text to Columns to remove time?

Yes, you can use the INT function. For example, if your date and time is in cell A1, entering =INT(A1) in another cell will return just the integer portion of the date serial number, effectively removing the time. You can then copy and paste the result as values.

What if my dates are in a different format like DD/MM/YYYY?

In the final step of the Text to Columns wizard, simply choose 'DMY' (Day, Month, Year) instead of 'MDY' from the drop-down list to correctly match and process your original data format.

Will Text to Columns overwrite my existing data?

By default, Text to Columns overwrites the data in the destination specified in the wizard, which defaults to your original column. If you want to keep the original data alongside the new data, you can change the 'Destination' cell reference in the final step of the wizard.

Document & File Issues