How To Remove Time From Date In Excel: A Definitive Guide To Datetime Cleanup

To remove time from a date-time stamp in Excel, you must either change the cell's visual formatting to a Date style or strip the decimal portion of the serial number using the INT function. Because Excel stores dates as whole numbers and times as decimal fractions, stripping the decimal guarantees absolute precision for downstream database lookups and calculations. Choosing the correct approach depends on whether your project requires a visual-only change or a complete underlying data conversion.

Data Auditing and Pre-Cleanup Preparation

In Microsoft Excel, date and time values are not stored as text strings; they are saved as sequential serial numbers. The integer portion of the serial number represents the number of days elapsed since January 1, 1900. The decimal fraction represents the time of day as a portion of a 24-hour cycle. For example, the datetime value of October 27, 2023, at 12:00 PM is stored internally as the floating-point number 45226.50, where 45226 represents the date and 0.50 represents exactly half of a day (12 hours).

When you perform data lookups using functions like VLOOKUP, XLOOKUP, or MATCH, these functions search for exact matches based on these underlying serial numbers. If your lookup value is a pure date (such as 45226.00) and your lookup array contains datetime stamps (such as 45226.50), your formulas will return error messages even if both cells visually display the exact same date. Cleaning and standardizing your datetime columns is a critical prerequisite for accurate data aggregation, pivot tables, and statistical modeling.

Pre-Processing Checklist

• Essential Software & Tools: Microsoft Excel (Office 365, Excel 2021, Excel 2019, or Excel Online).
• Prerequisite Data Literacy: Basic knowledge of entering formulas, copy-pasting values, and accessing the Format Cells menu.
• Estimated Completion Time: 2 to 10 minutes, depending on dataset size.
• Storage Warning: Always duplicate your raw date column before performing destructive transformations like Text to Columns or Find and Replace.

Five Professional Workflows to Remove Time from Dates

Step 1: The INT Formula Method (Mathematical Decoupling)

The most robust way to strip a timestamp from a date in Excel is using the INT function. This function rounds a real number down to the nearest integer. Since time is represented as a decimal fraction, the INT function strips away the fractional hours, minutes, and seconds, leaving only the pure date serial number.

1. Open your Excel worksheet and locate the column containing your datetime stamps (for this example, we will assume the data is in Column A, starting at cell A2).
2. Insert a new empty column to the right of Column A to act as your helper column.
3. In cell B2, type the following formula: =INT(A2)
4. Press Enter. You may see a five-digit number (such as 45226) or a formatted date without the time.
5. Select cell B2, hover your cursor over the bottom-right corner of the cell until the fill handle (a small black plus sign) appears, and double-click to drag the formula down to the end of your dataset.
6. To convert these formulas into static data, highlight the new column, press Control + C to copy it, right-click on the selected area, and choose "Paste as Values" (the clipboard icon with 123) from the context menu.

Pro-Tip: If your formula returns a #VALUE! error, your source date is likely formatted as text rather than a true serial number. You must convert the text strings to numbers before applying the INT function.

Step 2: The Visual Formatting Technique (Display Only)

If you do not need to use your dates in exact-match lookup formulas and simply want to clean up your reports for print or visual presentation, you can change the cell formatting to hide the timestamp.

1. Highlight the entire range of cells or the column header containing your datetime values.
2. Press Control + 1 on your keyboard to open the Format Cells dialog box (or right-click and select Format Cells).
3. In the Category list on the left side of the Number tab, click on "Date".
4. In the Type box on the right, scroll through the list and select your preferred date display format (such as March 14, 2012, or 2012-03-14).
5. Click OK to apply the changes.

Warning: This method is purely aesthetic. The formula bar will still display the underlying hours, minutes, and seconds. If you build a Pivot Table or run a SUMIFS formula using these cells, Excel will still group and calculate the data based on the hidden time values.

Step 3: The Find and Replace Wildcard Trick (Instant Text Truncation)

When your dates are imported as text strings from external systems (such as Salesforce, SAP, or SQL databases) and follow a consistent format like "10/27/2023 14:30:00", you can use wildcard characters to rapidly purge the time elements.

1. Highlight the target column containing your text-based datetime values.
2. Press Control + H on your keyboard to open the Find and Replace dialog window.
3. In the "Find what" text field, type a single space character followed by an asterisk: ** ***
4. Leave the "Replace with" text field completely blank.
5. Click the "Replace All" button. Excel will find the space character between the date and the time, select everything after that space, and replace it with nothing.

Pro-Tip: The asterisk is a wildcard character that represents any sequence of characters. By searching for a space followed by an asterisk, you tell Excel to delete the space and everything that follows it in each cell.

Step 4: The Text to Columns Tool (Data Split Technique)

For large, imported CSV files with thousands of rows, the Text to Columns tool is an efficient way to split dates and times into separate columns, allowing you to discard the time column afterward.

1. Select the column containing your datetime stamps.
2. Ensure there is at least one blank column immediately to the right of your selection to prevent existing data from being overwritten.
3. Navigate to the Data tab on the Excel Ribbon.
4. Click on the "Text to Columns" button located within the Data Tools group.
5. In the Wizard step 1, select the "Delimited" radio button and click Next.
6. In Step 2, check the box for "Space" as your delimiter, and uncheck all other delimiters. You will see the preview window split the date and time into separate columns. Click Next.
7. In Step 3, click on the first column in the Data Preview area (which contains your dates) and select "Date" in the Column Data Format section. Choose the correct date order (like MDY or YMD) from the adjacent dropdown menu.
8. Click on the second column in the Data Preview area (containing the times) and select the "Do not import column (skip)" radio button.
9. Click Finish to execute the split and discard the time data.

Step 5: The TEXT Function Method (String Conversion)

If you need to merge your dates with text strings in sentences or labels, using the TEXT function allows you to extract only the date portion as a clean, standardized string.

1. In an empty column next to your datetime data, click on cell B2.
2. Enter the following formula to output a standard dash-separated date: =TEXT(A2, "yyyy-mm-dd")
3. Alternatively, if you want a slash-separated US date format, use this formula instead: =TEXT(A2, "mm/dd/yyyy")
4. Press Enter and drag the formula down your column.

Warning: The output of the TEXT function is a text string, not a numeric serial date. This means you will not be able to sort the column chronologically unless you convert the text values back into serial numbers, or format the text to use the YYYY-MM-DD format, which naturally sorts alphabetically in chronological order.

Methodology Matrix: Data Types and Structural Impact

The table below summarizes how each of the five methods transforms your data, helping you choose the right approach for your specific analytical requirements.

Resolving Common Datetime Conversion Failures and Glitches

Scenario 1: The cell displays a five-digit number like 45226 instead of a date

• Root Cause: The INT function stripped the decimal time, but Excel reset the cell format to the General style, revealing the raw numeric serial representation of the date.
• Actionable Fix: Select the cell or column. Press Control + Shift + # (the hash key) on your keyboard to instantly apply the standard Short Date format, or select "Short Date" from the Number format dropdown in the Home tab on the Ribbon.

Scenario 2: The INT formula returns a #VALUE! error

• Root Cause: The source datetime values are stored in your worksheet as static text strings rather than numeric Excel serial dates. Because Excel cannot perform mathematical operations on text, the INT function fails.
• Actionable Fix: You must convert the text strings to actual dates. Highlight the column, select the Text to Columns tool, click Next twice, select "Date" as the column format in Step 3, and click Finish. Once converted to numeric dates, your INT formula will work perfectly.

Scenario 3: Your XLOOKUP or VLOOKUP returns #N/A errors after applying formatting

• Root Cause: You used the Visual Formatting method to remove the times. Because this method only changes what you see on the screen, the underlying decimal fractions are still present in Excel's memory, preventing an exact match with pure dates.
• Actionable Fix: Use the INT function method or the Text to Columns tool to permanently remove the decimal portions of your datetimes, ensuring a perfect exact-match search.

Scenario 4: Find and Replace deletes both the date and the time from your cells

• Root Cause: You typed the search parameters incorrectly. If you type only an asterisk in the "Find what" box, Excel will find and delete every character in the selected cells.
• Actionable Fix: Undo the change immediately by pressing Control + Z. Re-open the Find and Replace dialog and make sure you type exactly one space followed by an asterisk (** ***) to target only the time portion of the text.

Frequently Asked Questions

How do I remove time from a date in Excel without losing the date format?

Use the Format Cells dialog (Control + 1) to select a Date category layout. This visually hides the timestamp while retaining the underlying datetime value, allowing you to easily switch formats back later if needed.

Why is Excel still showing time even after I changed the cell format?

This occurs because the selected date style explicitly includes a time format. To fix this, open the Format Cells dialog, navigate to the Custom category, and delete any characters like hh:mm or AM/PM from the Type input line, leaving only yyyy-mm-dd or mm/dd/yyyy.

How do you bulk remove timestamps from thousands of rows in Excel?

The fastest way to clean thousands of rows is by using the Text to Columns tool. It processes columns in bulk in less than a second, stripping times and formatting dates without needing helper columns or copy-paste operations.

What is the fastest keyboard shortcut to strip times from dates?

While there is no single keyboard shortcut to strip times, you can quickly achieve this by entering the INT formula, double-clicking the fill handle, copying the helper column, and pressing Alt + E + S + V followed by Enter to paste values.

Does Power Query handle date-time splitting better than standard formulas?

Yes, Power Query is the ideal choice for recurring imports. When you load your database table into Power Query, you can simply right-click the datetime column header, select "Change Type", and choose "Date" to instantly strip times from all future data refreshes.

Streamline Your Business Analytics Workflows

Cleaning up messy data is the first step toward building fast, reliable dashboards and reports. Master your business forecasting and budgeting workflows by establishing strict data formatting rules across all your corporate worksheets.