How to Remove Time from Date in Excel (SOLVED)

Timestamps in Excel typically contain both the date and the time. We often, however, disregard the time and only use the dates in timestamps.

In this tutorial, I will show you varying ways you can do to remove the time – both temporarily and permanently.

Before I give a breakdown of the various methods to do this, I have a little trivia for you.

Good to Know

Did you know that in Excel, dates and times are stored as numbers?

You will see this when you change the format of cells containing dates into General (as shown below).

Date and Time in General format

The integers represent the date, while the decimals represent the time.

This little information will help you deal with date and time values later on.

1. Changing the Cell Format

Use this method if your goal is to hide the time from the cells and not actually remove it, per se.

1. Select all the cells containing the timestamps.

Select all cells containing timestamps

2. From the Home menu, click on the small arrow on the right side of the Number section.

Steps to open the "Format Cells" menu

This will open the Format Cells menu.

3. Select a date format that excludes time.

Select a date format that excludes time

Once selected, click OK.

4. That’s it! The cells will now display just the date.

Sample output after changing cell format to date

Note that when you click on one of these cells, the time will appear in the formula bar.

The time will appear on the formula bar whenever you click on a cell containing a timestamp (even if the time does not appear in the cell because of the cell format)

Important Notes:

  • Note that this method does not permanently remove the time but only hides it from plain sight.
  • When you click on one of these cells and view the formula bar, you will see that the time is still there.
  • If you use these cells as references to formulas, remember that Excel treats them as date and time.

2. Using a formula

If your goal is to extract the date from the timestamp, then this next option is for you.

Note that this approach requires you to have a separate column for the formulas. If you prefer working only on the column that has timestamps, please proceed to the next option (Find and Replace).

There are four Excel functions available that can extract date. The format of the result for each of them varies, so see what fits your needs.

INT() or TRUNC()

Both of these functions return the integer part of a cell. They disregard the decimal part.

As mentioned in the trivia above, the integer represents the date, while the decimal represents the time.

Therefore, when you use either of these functions, you can get the date value from the timestamp.

=INT (number)

Sample INT() formula to extract the date from timestamp

=TRUNC (number, [num_digits])

Sample TRUNC() formula to extract the date from timestamp

Once you click enter, the date of the timestamp should appear.

Now, the result will depend on how your cells are formatted. If your cells have a timestamp format, you will see something like this:

Sample result of INT() and TRUNC() formulas if cell is in timestamp format

Note that the time is still there, with 12:00 AM as its value — this is equivalent to a zero value for time.

If your cell is in General format, you’ll see a whole number appear (like the image below).

Sample result of INT() and TRUNC() formulas if cell is in General format

If this is the case, you’ll need to change the format of these cells with the appropriate date format.

To do these, highlight all the cells and press CTRL + 1 (this is a shortcut to opening the Cell Format menu). Select the appropriate date format and click OK. You should now see your cells with just the date.

Sample output after applying the INT() or TRUNC() formulas to the cells and changing the format to Date

In case you’re wondering, these two functions are practically the same, except that the INT() function rounds a number down to the nearest integer.

The TRUNC() function, on the other hand, truncates a number to an integer by removing the decimal (without rounding it off).

They may differ in the way that they handle the decimals, but it doesn’t really matter if you use them to extract the dates.


The TEXT() function converts a value to a text in a specific number format.

It uses this syntax: =TEXT (value, format_text)

  • value
    • refers to the value or cell to be formatted
  • format_text
    • refers to how the value will be formatted
    • since we are working on dates, we will use:
      • m – for month
      • d – for day
      • y – for year
Sample TEXT() formula to extract date from timestamp

NOTE: This function converts the timestamp into text. You will not be able to perform date calculations on these cells unless you first revert them to date format.

If you’re not concerned about converting them to actual dates, you can stop here.

But if you want to convert day, month or year as dates, you will need to add the DATEVALUE() function.


The DATEVALUE() function converts a date in text form to a number that represents the date in Microsoft Excel date-time code.

It uses this syntax: =DATEVALUE (date_text)

Since we already have the text format of the date after adding the TEXT() function, we only need to add DATEVALUE() in our formula.

It should look something like this:

Sample DATEVALUE() formula to extract date from timestamp

3. Using Find and Replace 

This last option is great if you’re not much of a fan of formulas and would prefer to directly apply the changes to the cells containing timestamps.

Note that this method will only work if your timestamp is formatted like this: [date] [space] [time]

Example: 2/4/2028 4:46:53 AM

** The space between the date and time is required.

1. Select all cells containing the timestamps.

Select all cells containing timestamps

2. Press CTRL + H to open the Find and Replace menu. 

In the Find What textbox, type a space followed by an asterisk “ *”

Add a space and an asterisk (*) at the Find What textbox and click the "Replace All" button

Leave the Replace textbox blank and click on the Replace All button.  

3. Notice that all the time values in your cells are now 12:00 AM — this is the zero equivalent of time.

Sample output after replacing the space and asterisk with nothing

4. Close the Find and Replace menu.

5. Change the format of the cells by pressing CTRL + 1 and selecting the appropriate date format.

Select the appropriate date format

6. Once that is set, you should now only have the dates inside your cells.


As you have seen in the examples above, removing the time from the date or timestamps in Excel is not that hard. Various options are available, and you can decide which best meets your needs.

Leave a Comment