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:
Open your Excel spreadsheet.Select the cells containing your timestamps. In the Home menu, select the downwards arrow at the end of the Number Format box. Select one of the date formats.
Once you change the format, the times will stop appearing in your cells. If you click on one of the cells, the time format is still visible in the formula bar.
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:
Open your Excel spreadsheet.Select the cell or cells containing your timestamps. Right-click and select Format Cells. Alternatively, you can use the shortcut Ctrl+1 on Windows or Cmd+1 on Mac. In the Number tab, click on Date and select a date format that does not include the time. 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.
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:
Click in the cell where you want the edited date to appear.Type =INT( Select the cell containing the timestamp (or type the cell reference manually). 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.
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. Comment
Δ
