Function Problems

How to Remove Time from a Date in Excel (Keep Only Date)

WPS Editor Oct 1, 2026 874 views

Question details

The user needs to extract only the date from a combined date-time value or text string in Excel.

How to Remove the Time from a Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Cleaning up spreadsheet data that includes timestamps (e.g., '01-Jun-23 11:07:23:878') to retain just the date portion for cleaner reporting.
Observed behavior: The cell contains both date and time information, which may be stored as a true date-time value or as a text string.

Before you start

Before proceeding, determine if your dates are stored as text or actual date-time values, and create a backup of your dataset to prevent accidental data loss during bulk replacements.

Solution 1Recommended

Use Find and Replace to Remove Time from Text Dates

A quick and efficient method to remove the time portion from text-based dates across multiple cells simultaneously.

This method is highly effective when your date and time data is stored as text. It uses a wildcard character to locate the space separating the date and time, and deletes everything after it.

Select the Data Range

Highlight the cells or the entire column containing the date and time values you want to modify.

Open Find and Replace

Press Ctrl + H on your keyboard to open the Find and Replace dialog box.

Enter the Search Criteria

In the 'Find what' box, type a single space followed by an asterisk (*). Leave the 'Replace with' box completely empty.

Replace and Format

Click 'Replace All' to remove the time segment. Afterward, right-click the selected cells, choose 'Format Cells', and apply a standard Date format.

Use Find and Replace to Remove Time from Text Dates

Solution 2

Extract the Date Using the TEXTBEFORE Formula

Ideal for extracting the date portion into a new column without altering your original data structure.

Efficient Data Cleaning

Clean and Format Your Spreadsheet Data with WPS Office

WPS Spreadsheet offers powerful data formatting tools, seamless Find and Replace capabilities, and full support for advanced text and date formulas to help you process your data efficiently.

1. Open Your Data: Launch WPS Spreadsheet and open the document containing your date and time values.
2. Apply the Formula: In an adjacent column, type =TEXTBEFORE(A2, " ") for text strings, or =INT(A2) for true date values, then press Enter.
3. Format as Date: Select the result cells, press Ctrl + 1 to open the Format Cells dialog, and apply your preferred Date format.

100% compatible with Microsoft Excel (.xlsx, .xls) files and formatsFull support for advanced data extraction formulas like TEXTBEFORE and INTLightweight installation with smooth performance on older devicesFree to use for daily spreadsheet tasks and data analysis

microsoft office alternative - wps office

Frequently Asked Questions

How do I remove the time from a true date-time numeric value?

If your data is stored as a true date-time value rather than text, use the INT function. Type =INT(A2) in an empty cell. Because Excel stores dates as whole numbers and times as decimals, the INT function removes the decimal (time) portion. Remember to format the result as a Date.

Why isn't the Find and Replace method working on my dates?

The Find and Replace method with the space and asterisk wildcard (* ) only works if your data is stored as text. If your cells are actual date-time values, Find and Replace will not recognize the space. You must use the =INT() formula or change the cell formatting to display only the date.

Can I remove the time by just changing the cell format?

Yes, you can hide the time visually by selecting the cells, right-clicking to choose 'Format Cells', and selecting a 'Short Date' format under the Number tab. However, keep in mind that this only changes how the data looks; the underlying time value is still preserved in the cell.

Excel & Data Problems