How to Remove Time from Date/Timestamp in Excel (6 Easy Ways)

One common thing many people need to do often in Excel is to remove time from dates (i.e., remove the time from the time stamp so that you only have the date).

This comes up a lot when you want to count, group, or match entries by day, and the time part keeps getting in the way.

Excel allows you to display data in many different formats. And there are way too many to choose from when it comes to dates and time data in Excel.

And since there are so many formats, there are also ways to edit and modify these.

Below is an example where column C has the timestamps (with date and time both) and column D only has the date value.

In this tutorial, I will show you six ways to remove time from the date in Excel. These include a formatting trick, the INT formula, Find and Replace, Text to Columns, a formula for timestamps stored as text, and Power Query.

One thing you need to know before I jump into the methods is that date and time are stored as numbers in Excel. While you may see a date January 01, 2020, in reality, it’s the number 43831 at the backend.

Similarly, time is also stored as numbers. For example, January 01, 2020 3:00 PM is the number 43831.625 in the backend (where the integer part represents the date and decimal part represents the time).

Change the Format to Hide the Time from Timestamp

If all you want is to show the date and not the date and time both, you can easily do that by altering the format of the cells in Excel.

This means that you do not actually remove the time from the time stamp, but only hide the time part.

Below is a list of help-desk tickets where columns C and D have the exact same values. But since these are formatted differently, you see the time in column C and not in column D.

As you can see in the image above, with a cell in column D selected, the formula bar shows the date and time both while the cell only shows the date.

Below are the steps to remove/hide the time from the date by changing the cell formatting:

1. Select the cells in which you have the time stamp from which you want to remove the time (C2:C11 in our example).

2. Hold the Control key and then press the 1 key. This will open the Format Cells dialog box.

3. In the Format Cells dialog box, select the Number tab.

4. In the left pane, click on the Date option.

5. In the options on the right, select the format in which you want the date to be displayed.

6. Click OK.

The above steps would instantly hide the time part from the time stamp and you will only see the date.

Remember that this method only hides the time part and doesn’t remove it. This means that if you use these cells in any calculations, it would include the date as well as the time value.

This hidden time is also a common reason why comparing dates in Excel gives unexpected results.

There are two good things about this method:

• You don’t need a separate column to get the result. Just select the cells with the data and change the format for those cells itself.
• It doesn’t alter the original value, it only changes the way it’s being displayed. So in case you need to use the original data, you still have it intact.

In case you want to remove the time portion from the time stamp completely, better to use the formula method (covered next) or the Find and Replace method or the Text to Columns method (covered after that).

Also read: Calculate Time In Excel

Remove Time from Date in Excel Using the INT Formula

The INT formula is the easiest way to remove the time portion from the time stamp and only get the date portion.

Below is the same list of help-desk tickets with the date and time each ticket was opened, and I want to get only the date in column D.

Below is the INT formula that can do this:

=INT(C2:C11)

The above formula takes the cell values as the input and returns only the integer part and removes the decimal part.

Since dates are stored as integer values and time is stored as fraction/decimal in Excel, when you remove the decimal part, you are only left with the date part.

You only enter this formula in cell D2, and the results spill down to D11 automatically.

If you are using Excel 2019 or earlier, enter the below formula in cell D2 and then copy it down to D11:

=INT(C2)

But you may still see the time part (12:00 AM) in the result… why is that?

That’s because of the format of the cell, which is forcing it to show the date as well as the time. And since the time value is 0, it shows 12:00 AM.

You may also see plain numbers such as 46083 in the cells below D2. That’s because cells the result spills into keep the General format, so they show the date as its backend number.

So now to not show the time part (or the plain numbers), you will have to change the format to only show the date and not the time.

Here are the steps to only show the date and not the time in Excel:

1. Select the cells with the results (D2:D11).

2. Click the Home tab.

3. In the Number group, click on the drop-down.

4. Select Short Date (or Long Date), based on how you want the date to be displayed.

That’s it!

You have successfully removed the time from the date.

Remember that with this formula, you have actually changed the value by getting rid of the decimal part. This is different than the method covered before this (where we simply changed the format to hide the time portion).

Now, let’s see another Excel formula to do this.

You can also use the TRUNC function here. =TRUNC(C2:C11) gives exactly the same result, since it also cuts off the decimal part.

Remove Time From Date using Find and Replace

If your time stamp data has a format where the date is followed by a space and then the time, you can use the find and replace functionality to quickly get rid of the time portion.

Below is the same list of help-desk tickets, and I want to remove the time from the timestamps in column C itself.

Below are the steps to remove time from date using Find and Replace:

1. Select the cells from which you want to remove the time (C2:C11).

2. Click the Home tab.

3. In the Editing group, click on the Find & Select option.

4. Click on the Replace option. This will open the Find and Replace dialog box.

5. In the Find what: field, enter * (a space character followed by the asterisk symbol).

6. Leave the Replace with: field blank.

7. Click on Replace All.

The above steps would instantly remove the time part from the cell and you will be left only with the date.

This works because the asterisk is a wildcard character that stands for any number of characters. So a space followed by an asterisk matches the space after the date and everything that comes after it, which is the time.

But you may still see the result where the date also has the time portion (although all the time values are 12:00 AM).

This happens as we have gotten rid of the time value from the overall date, but we still haven’t changed the format to only show the date and not the time.

To only get the date and not show the time part, keep the cells selected and change the format to Short Date (or Long Date).

You’ll find this in the drop-down in the Number group on the Home tab. These are the same steps we used for the INT formula above.

Note that this works only when you have the data in a format where the date is followed by a space character and then the time.

In case you have leading spaces, this method will not work. The space before the date also matches, so Find and Replace wipes out the whole entry and leaves the cell blank.

So you will first need to get rid of leading spaces.

One benefit of using Find and Replace over formulas is that you don’t need an extra column to get the result. You can have the results in the same cells where you had the original data.

Just make sure you create a backup copy in case you may need the original data in the future.

Also read: How to Convert Seconds to Minutes in Excel (Easy Formula)

Remove Time From Date using Text to Columns

Text to Columns is mostly used to split text in a cell.

And in this example, I will use it to split the time stamp into date and time. Once done, I will only be left with the date part, which I can then format to show the date however I want.

Below is the same list of help-desk tickets, and I want to remove the time from the timestamps in column C itself.

Below are the steps to use Text to Columns to remove the time portion from the date:

1. Select the cells from which you want to remove the time (C2:C11).

2. Click the Data tab.

3. In the Data Tools group, click on the Text to Columns option. This opens the Convert Text to Columns Wizard.

4. In Step 1 of 3, select the Delimited option and click Next.

5. In Step 2 of 3, select Space as the delimiter and click Next.

6. In Step 3 of 3, click on the column that contains the time values in the Data preview section, and then select the ‘Do not import column (skip)’ option. Do the same for the AM/PM column next to it. Keep the destination cell the same where you have the data.

7. Click on Finish.

The above steps would remove time portion from the cells and you will only be left with the date.

Since our timestamps show AM and PM, the space delimiter splits each one into three parts (the date, the time, and AM/PM). That’s why you skip two columns in Step 3.

If your timestamps use 24-hour time (like 15:20), there’s only one time column to skip.

However, you will still see the time with each date (where the time will be 12:00 AM). This is because the format of the cells still forces the time to be shown.

To change the format so it only shows the date, select the cells and pick Short Date (or Long Date) from the drop-down in the Number group on the Home tab.

With Text to Columns, you can also get the resulting data in separate cells (and keep the original data as is). To do this, you need to specify the destination cell in Step 3 of the Text to Columns wizard.

Remove Time from a Text Timestamp Using DATEVALUE

All the methods so far work when Excel sees your timestamps as real dates and times. But data exported from another system (like a booking site or a database) often comes in as text that only looks like a timestamp.

An easy way to spot this is that the timestamps are left-aligned in the cells, while real dates are right-aligned by default.

And if you use the INT formula on these, you get the #VALUE! error, because there is no number for INT to work with.

DATEVALUE is the opposite. It only takes one single argument, but it needs to be the date in the text format, so if you give it a cell that contains a real date, it returns the #VALUE! error.

Below is a list of web bookings exported from a booking system, where the timestamps in column C are text, and I want to get the booking date in column D.

Below is the formula that will do this:

=DATEVALUE(LEFT(C2:C11,10))

You only enter this formula in cell D2, and the results spill down to D11.

How this formula works:

• LEFT(C2:C11,10): The LEFT function takes the first 10 characters from each timestamp. So 2026-04-06T07:42:18Z becomes 2026-04-06, which is just the date part of the text.
• DATEVALUE(…): The DATEVALUE function has one job, to give you the value of the date from a date in the text format. It turns 2026-04-06 into a real date that Excel can use in calculations.

Below is the syntax of the DATEVALUE formula:

=DATEVALUE(date_text)

DATEVALUE ignores any time in the text. So if your timestamps look like 2026-04-06 07:42:18, =DATEVALUE(C2:C11) alone gives you the date.

Here you need LEFT because the T and the Z make the full text something DATEVALUE can’t read, and it returns the #VALUE! error.

DATEVALUE gives you the result as a number, so you may see values like 46118 instead of dates. Select D2:D11 and change the format to Short Date, just like we did with the INT formula.

If you are using Excel 2019 or earlier, enter the below formula in cell D2 and then copy it down to D11:

=DATEVALUE(LEFT(C2,10))

This formula works when the text starts with the date in the yyyy-mm-dd form (the ISO 8601 format), which is a common format for exported timestamps.

You may have seen a formula that combines DATEVALUE with the TEXT function, like =DATEVALUE(TEXT(A2,"dd-mm-yyyy")), suggested for removing time from a date.

Avoid it, because it depends on your regional date settings. With US settings, it turns March 4 into April 3 (TEXT gives 04-03-2026, which US Excel reads month first), and it gives the #VALUE! error for November 25 (there’s no month 25).

Also note that the Z at the end of these timestamps means the time is in UTC.

So the date you get is the UTC date, which can be one day off from the local date for bookings made late in the evening in the US.

If your text dates come in a different shape, check out this guide on how to convert text to date in Excel.

Remove Time from Date Using Power Query

If you get this data regularly (say, a weekly export of tickets), Power Query is a good option. You set up the steps once, and the next time you only need to refresh.

Below is the same list of help-desk tickets, and I want to get a copy of this data where the Opened At column only has the date.

Here are the steps to remove the time from the date using Power Query:

1. Select any cell in the dataset.

2. Click the Data tab, and in the Get & Transform Data group, click on From Table/Range.

3. In the Create Table dialog box, make sure ‘My table has headers’ is checked and click OK. This opens the Power Query Editor.

4. Click the data type icon at the left of the Opened At column header and select Date.

5. In the Change Column Type dialog box, click on Replace current.

6. Click the Home tab and click on Close & Load.

The above steps load the result in a new worksheet, where the Opened At column only has the dates.

The original data stays as it is. When new tickets get added to it, right-click any cell in the result table and choose Refresh, and Power Query removes the time from the new rows as well.

These are some simple and quick ways to remove time from date in Excel. The method you choose will depend on whether you want to keep the original data or not.

When you use formulas or Power Query, it will give you the result in a separate place. When you change the format or use Find and Replace or Text to Columns, it will give you the result in the same cells itself.

Hope you found this Excel tutorial helpful.

Other Excel tutorials you may also like:

• How to Calculate the Number of Days Between Two Dates in Excel
• How to Insert Timestamp in Excel
• Convert Date to Text in Excel
• How to use Excel DATEDIF Function
• Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)
• How to Stop Excel from Changing Numbers to Dates
• How to Convert Serial Numbers to Dates in Excel
• Combine Date and Time in Excel