How-To

How to Remove Time From a Date in Excel

How to Remove Time From a Date in Excel

Need to remove the time from a timestamp in Excel to just view the date? You can hide it in a number of ways. Here’s how.

Let’s picture a scenario. You’re importing timestamped data into Excel or perhaps pulling it in from another sheet. Everything looks fine, except for one thing—your cells show the time and the date, which your boss simply doesn’t want. Fortunately, there are a few ways to remove the time from a date in Excel.

Most of the time (and that isn’t a pun), the time isn’t important, but the date value is. Since the timestamp contains both, Excel will display both until you decide to remove the time manually. If you want to remove the time from a date in Excel, here’s what you’ll need to do.

Change the Date Format in Excel Using The Number Formatting

The easiest way to remove the time from a date in Excel is to change the number formatting. This doesn’t remove the time from the timestamp—it merely stops it from displaying in your cells. If you use these cells in calculations, both the time and the date are still included.

  1. Open your Excel spreadsheet.
  2. Select the cells containing your timestamps.
    Select cells in Excel
  3. In the Home menu, select the downwards arrow at the end of the Number Format box.
    Excel number format options
  4. Select one of the date formats.
    Date formats in Excel

Once you change the format, the times will stop appearing in your cells.
Dates without times in Excel

If you click on one of the cells, the time format is still visible in the formula bar.
Excel timestamps with dates and times

Change the Date Format in Excel Using Format Cells

The method above only gives you two time and date options to choose from. If you want more control over the way your information is displayed, you’ll need to use the Format Cells method to remove the timestamp for your dates in Excel.

  1. Open your Excel spreadsheet.
  2. Select the cell or cells containing your timestamps.
    Select cells in Excel
  3. Right-click and select Format Cells. Alternatively, you can use the shortcut Ctrl+1 (one) on Windows or Cmd+1 (one) on Mac.
    Formatting cells in Excel
  4. In the Number tab, click on Date and select a date format that does not include the time.
    Date formats in Excel
  5. Click OK to confirm.

The dates are now displayed without the time, but if you click on a cell, the time still appears in the formula bar.

Excel timestamps with dates and times

Remove the Time From a Date in Excel Using INT Function

If you want to completely remove the timestamp from a date in Excel, you can use the INT function to do so. That’s because the timestamp is in the form of a decimal. Everything before the decimal point represents the date and everything after represents the time.

The INT function removes everything after the decimal point, stripping out the time information but leaving the date information.

  1. Click in the cell where you want the edited date to appear.
  2. Type =INT(
    Writing an INT formula in Excel
  3. Select the cell containing the timestamp (or type the cell reference manually).
    Selecting a cell in Excel
  4. Type a final closed bracket and press Enter.

Your result will now show a time of 12:00 AM. This is because the cell is still in a format that shows both date and time. Since the time portion is now zero, it shows this as 12:00 AM.

Removing time from a timestamp in Excel

To remove the time completely, follow the steps in the section above to change the date format to one that does not include the time.

Making Changes in Excel

Knowing how to remove the time from a date in Excel allows you to display your data exactly how you want it. The software is powerful but sometimes requires a bit of tweaking to get your information displayed in an efficient, understandable way. Fortunately, the options are there to display everything just how you want.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top