How-To

How to Remove Time From a Date in Excel

Excel featured

Need to remove the time from a timestamp in Excel? You can hide it using number formatting or use INT to remove it entirely. Here’s how.

Let’s picture a scenario. You’re importing timestamped data into Excel.

Everything looks fine, except for one thing—your cells show the time and the data, which your boss simply doesn’t want.

Most of the time (and that isn’t a pun), the time isn’t important, but the data 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.

To change the date format in Excel using number formattinG:

  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/date options to choose from. If you want more control over the way your data is displayed, you’ll need to use the Format Cells method.

To change the date format in Excel using advanced cell formatting:

  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 on Windows or Cmd+1 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 Using INT Function

If you want to completely remove the time information from the timestamp, 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.

To remove the time from a date using INT in Excel:

  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.

If your dates aren’t showing enough information, you can add months to a date in Excel. If your timestamps are showing as text, you can also convert text to a date. You can also truncate text in Excel if you need to remove unnecessary parts of a text string.

Click to comment

Leave a Reply

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

 

To Top