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: 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: 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: 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 Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
