Remove Time From Date in Excel

Last updated: August 23, 2026

Do you have unwanted time values showing up alongside dates in Excel?

This can clutter your view and make data analysis harder when working on large datasets. It also quietly breaks things: a lookup, a filter or a pivot table that matches on a date will miss every row where a hidden timestamp is still attached. In this guide you will learn six ways to delete, trim or get rid of a time stamp on a date, and how to tell which one you actually need.

What this guide covers

1. Why remove time from a date in Excel?
2. Hiding the time is not the same as deleting it
3. Method 1: Remove the time with INT
4. Method 2: Hide the time by changing the number format
5. Method 3: Make it permanent with Paste Special
6. Method 4: Strip a whole column with Text to Columns
7. Method 5: Remove the time in Power Query
8. Method 6: Use DATEVALUE when the date is text
9. How do you keep the time and remove the date instead?
10. How do you split a datetime into two columns?
11. Which method should you use?
12. Why is the time still there?
13. Can ChatGPT remove time from dates in Excel?
14. FAQ

Why remove time from a date in Excel?

Excel often stores dates and times in the same cell, creating problems when calculating or organizing data. Whether you're cleaning up imported data or simplifying a spreadsheet, removing the time portion is often necessary to avoid errors.

The reason is how Excel stores the value. A date is a whole number counting days, and the time is the decimal fraction after it. 4 October 2024 at 14:30 is not the same value as 4 October 2024, it is that value plus 0.604166. So any operation that compares dates, a VLOOKUP, a filter, a pivot table row grouping, a COUNTIF, treats those two as different days even though they look identical on screen.

Hiding the time is not the same as deleting it

This is the single most important thing on this page, and it is where most people go wrong.

The rule: changing the number format changes what you see. It does not change what is stored. If you format a datetime cell as a Date, the time is still sitting in the cell and will still break your lookups. If you need the time gone for real, you need INT, Paste Special, Text to Columns or Power Query, not a format change.

Both are legitimate. Just pick deliberately.

Method 1: Remove the time with INT

The INT function extracts just the date portion from a cell that contains both date and time. The function converts a datetime value into an integer, effectively removing the decimal part (the time) and leaving only the date.

1. Select the cell to enter the formula: Click on the cell where you want the result (the date without the time) to appear. Let’s say you want the result in cell B1.

2. Enter the INT formula: In cell B1, type the formula =INT(A1)

3. Press Enter: After typing the formula, press Enter. The result will display only the date without the time. In this example, it should show:
10/04/2024.

4. Drag the formula (Optional): If you have multiple rows with date and time values (e.g., A2, A3, etc.), you can drag the formula down to apply it to other rows. Click on the small square at the bottom-right corner of cell B1 (this is the "fill handle"). Drag it down to apply the formula to the rest of the rows, e.g., B2, B3, and so on.

If the result comes back as a number like 45569, the formula worked and the cell is just formatted as General. Select it and set Home > Number Format to Short Date.

TRUNC does the same job

=TRUNC(A1) gives an identical answer for dates. INT and TRUNC only differ on negative numbers, and Excel dates are never negative, so use whichever you find easier to remember.

Method 2: Hide the time by changing the number format

By default, Excel may still display time in the result if the cell is formatted as a date-time format. Here's how to ensure Excel only displays dates:

1. Select the cells to format: Click on the cell or range of cells that contain the date and time you want to format (e.g., A1:A5). If you want to select multiple cells at once, click and drag over the cells or hold down Ctrl (Windows) or Cmd (Mac) while clicking each cell.

2. Open the Format Cells dialog box: Right-click on the selected cell(s) and choose Format Cells from the dropdown menu. Alternatively, you can use the keyboard shortcut Ctrl + 1 (Windows) or Cmd + 1 (Mac) to open the Format Cells dialog box.

3. Choose the Date Format: Go to the Number tab in the Format Cells dialog box. Select Date under the Category section on the left. On the right side, choose the desired date format from the Type list (e.g., MM/DD/YYYY or DD/MM/YYYY). This ensures only the date is displayed, hiding the time portion.

4. Click OK to apply the format: Click OK once you’ve selected your preferred date format. The cell(s) will now display only the date, even though the time is still stored in the background.

Read that last line again, because it matters. The time is still stored in the background. Click one of those cells and look at the formula bar: the time is right there. Use this method when you only need the sheet to look clean. Do not use it when the dates feed a lookup, a filter or a pivot.

Method 3: Make it permanent with Paste Special

INT gives you a clean date in a helper column, but you usually want it back in the original column with no formula attached. That is what Paste Special is for.

1. Put =INT(A2) in a spare column and fill it down the whole range.
2. Select the helper column and press Ctrl + C.
3. Right-click the first cell of the original column and choose Paste Special > Values (the 123 icon).
4. Delete the helper column. The original now holds real dates with no time and no formula.
5. Format the column as Short Date if it shows serial numbers.

The rule: paste Values, never Paste All. Pasting everything would drag the formula along and it would immediately break once you delete the helper column.

Method 4: Strip a whole column with Text to Columns

This is the fastest way to clean an imported column in place, with no helper column and no formula at all. It is built for exactly this job.

1. Select the column that holds your date and time values.
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next.
4. Tick Space as the delimiter. The preview splits the date from the time.
5. Click Next. In the preview, click the time column and choose Do not import column (skip). Click the date column and set it to Date with the right order (MDY or DMY).
6. Click Finish. The time is gone from the data, not just from the display.

Two warnings. If your times use AM or PM there will be a second space, so you may get three preview columns and need to skip two of them. And if the column to the right is not empty, Text to Columns will overwrite it, so insert a blank column first.

The Find and Replace shortcut

If the cells display the time, you can also open Find and Replace with Ctrl + H, type a space followed by an asterisk in the Find box, leave Replace empty, and click Replace All. The asterisk is a wildcard meaning "everything after this point", so the time is discarded and Excel re-reads what is left as a date.

It is fast, but it is blunt: it only works when the time is actually visible in the cell, and it rewrites your values with no undo beyond Ctrl + Z. Text to Columns is the safer choice on data you care about.

Method 5: Remove the time in Power Query

If the data arrives on a schedule, from an export or a database connection, do this once in Power Query and it repeats itself on every refresh.

1. Select your data and go to Data > From Table/Range.
2. In the Power Query Editor, click the header of the datetime column.
3. Go to Transform > Date > Date Only.
4. Click Close & Load.

If you would rather keep the original column intact, use Add Column > Date > Date Only instead, which appends a new date column and leaves the datetime alone.

Why this is worth the extra minute: every other method on this page has to be redone by hand the next time the file is refreshed. This one does not.

Version note: Power Query is built into Excel 2016 and later on Windows, and into Microsoft 365 on Mac. In Excel 2010 and 2013 it is a free Microsoft add-in you install separately.

Method 6: Use DATEVALUE when the date is text

The DATEVALUE function in Excel converts a date stored as text into an actual date value that Excel can recognize and use for calculations. If you have a date and time combined in a single cell as text, and you only want to extract and use the date, the DATEVALUE function can be useful.

However, it’s important to note that DATEVALUE works only when the date is in text format. Point it at a cell that already holds a real date and it returns #VALUE!.

1. Check if your date is in text format: If your date is stored as text, it may look like this in cell A1:
10/04/2024 14:30 (as text). You can verify whether a date is text by trying to change the format. If it doesn’t change or shows errors in calculations, it's likely stored as text. The quickest tell is alignment: Excel right-aligns real dates and left-aligns text by default.

2. Use the DATEVALUE function: In another cell (e.g., B1), use the formula =DATEVALUE(A1) to extract the date portion from the text and convert it into an Excel date. The program will ignore the time portion. Microsoft's own DATEVALUE reference confirms this: time information in the text argument is simply ignored.

3. Format the result as a date: After applying the DATEVALUE function, Excel will display the result as a serial number (for example 45569). Excel counts serial number 1 as 1 January 1900 and every day since, so a raw serial number is the expected output, not an error. To fix the display, format the cell as a date:

• Right-click on cell B1, select Format Cells.

• Choose Date from the options and select your desired date format (e.g., MM/DD/YYYY or DD/MM/YYYY).

• Click OK.

How do you keep the time and remove the date instead?

The reverse case is asked less often but it comes up on shift logs, call records and anything timestamped. The formula mirrors INT: subtract the whole-number date and keep the fraction.

=A2-INT(A2) returns the time on its own. Format the result cell as Time (Ctrl + 1 > Time) or it will show as a decimal like 0.604166.

=MOD(A2,1) does exactly the same thing in fewer characters, and is the version you will see most often in other people's workbooks.

Either result is a real Excel time value, so it will sort, filter and subtract correctly.

How do you split a datetime into two columns?

If you want the date in one column and the time in the next, combine the two formulas above. Nothing is lost, which is often safer than stripping the time outright.

1. In B2, enter =INT(A2) and format the column as Short Date.
2. In C2, enter =A2-INT(A2) and format the column as Time.
3. Fill both down.

Text to Columns does the same split without formulas: follow Method 4 but import the time column as General instead of skipping it, then format it as Time.

Which method should you use?

Why is the time still there?

Can ChatGPT remove time from dates in Excel?

It can write the formula or the script for you, which is useful on a large or unusual dataset. Treat it as a drafting tool and check the output, because the obvious answer it gives is not always the right one.

Generating a formula with ChatGPT

Prompt example:

"Can you provide a formula to remove the time portion from a datetime value in Excel?"

Output example:

ChatGPT may give you the formula: =TEXT(A1,"MM/DD/YYYY") to display only the date.

Check this one before you use it. Microsoft's TEXT reference states plainly that TEXT returns a text value. The cell will look like a date and behave like a word. It will sort alphabetically rather than chronologically, it will not subtract from another date, and a lookup against a real date column will miss it entirely. Use TEXT when you are building a label or joining a date into a sentence. For data you will sort, filter or calculate on, use =INT(A1) instead.

Creating a VBA script with ChatGPT

If you have a large dataset and want to automate the process, ask ChatGPT to generate a VBA script that removes the time from dates across an entire column.

Prompt example:

"Can you give me a VBA script to remove time from all dates in column A in Excel?"

Output example:

ChatGPT might generate this code:

Sub RemoveTimeFromDate()

Dim cell As Range

For Each cell In Range("A1:A100")

cell.Value = Int(cell.Value)

Next cell

End Sub

This script will loop through each cell in column A and remove the time portion, leaving just the date. It overwrites the original values with no undo, so run it on a copy of the file the first time.

Using AI for batch formatting instructions

Prompt example:

"How can I batch format all cells in column B to display only dates and remove time in Excel?"

Output example:

ChatGPT will guide you through Excel’s formatting options, explaining how to remove time visually without affecting the actual data. Note the wording: visually. That is Method 2 above, and the time is still in the cell.

Final Thoughts on "How To Remove Time From Date in Excel"

Six methods, and the choice between them comes down to one question: do you need the time hidden, or gone? Formatting hides it. INT, Paste Special, Text to Columns, Power Query and DATEVALUE remove it. If a lookup, filter or pivot table depends on those dates, you need it gone.

Once the dates are clean you can use them properly, whether that is subtracting dates to count days or counting months between two dates. Both of those break in exactly the same way when a hidden timestamp is still attached, which is usually why a date subtraction comes back with decimals.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ

What is the formula to remove time from a date in Excel?

Use =INT(A2). It keeps the whole-number day and discards the decimal fraction that holds the time. =TRUNC(A2) gives the same answer. Format the result as Short Date if it appears as a serial number.

Can I remove the time from dates without changing the data in Excel?

Yes, you can keep the time portion hidden by formatting the cells to display only the date. Press Ctrl + 1, choose Date and pick a format. Be aware that the time is still stored in the cell, so lookups, filters and pivot tables will still see it.

How do I delete the time stamp from a whole column at once?

Use Data > Text to Columns, choose Delimited, tick Space, then skip the time column and set the date column to Date. It cleans the column in place with no helper column and no formula.

Why does my date still have a time after I changed the format?

Because number formatting only changes the display. The time is still stored in the cell and the formula bar will show it. To remove it for real, use INT and then Paste Special > Values over the original, or use Text to Columns.

How do I remove the date and keep only the time?

Use =A2-INT(A2) or =MOD(A2,1). Both return the time fraction on its own. Format the result cell as Time, otherwise it displays as a decimal such as 0.604166.

What's the difference between using a formula and VBA to remove time from dates?

Formulas work on a per-cell basis and update automatically when the source changes. VBA can automate the task across many cells at once, but it overwrites the original values and cannot be undone once the file is saved.

How accurate is ChatGPT for generating Excel formulas?

ChatGPT can generate highly accurate formulas and scripts, but it’s always a good idea to test the solution on your dataset. For this particular task it often suggests TEXT, which returns text rather than a date and will break sorting and calculations.

Related Articles

How to Subtract Dates in Excel

How to Calculate Months Between Dates in Excel

How to Square a Number with AI

How to Pull Data from Another Sheet in Excel

How to Fix Excel Ran Out of Resources Error