The result in column D is the date only from the timestamps in column B. The time in the original timestamps is discarded. Note: This example requires valid dates. If you have dates in Excel that don’t seem to be dates, try formatting the cells with the General format. If the date is really a date, you’ll see a number. If the date is being treated as text in Excel, nothing will change.
Number format option
Before permanently removing the time portion of a date, it’s important to understand that you have the option of suppressing the time with a number format. For example, to display “06-Jun-2000 12:00 PM” as “06-Jun-2000”, you can apply a number format like this: This number format will show the date but hide the time. However, the time will still be there. If the goal is to permanently remove the time portion of a timestamp, see the formulas below. Note: Excel’s date formats are flexible and can be customized in many ways.
INT function
The INT function returns the integer part of a decimal number by rounding the number down to the integer. For example: Accordingly, if you have dates in Excel that include time, you can use the INT function to remove the time portion of the date. For example, assuming cell A1 contains the date and time, June 1, 2000 12:00 PM (equivalent to the number 36678.5), the formula below returns just the date portion (36678): Notice that the time portion of the date (the fractional part) is permanently discarded, leaving only the integer value. The screen below shows the original dates with the General number format applied, so you can see what is really happening with all of the dates:
Note: to see results formatted as a date, be sure to apply a date number format. Make sure you use a date format that does not include a time. Otherwise, you’ll see the time displayed as 12:00 AM even though the time value has been removed. This is normal Excel behavior.
TRUNC function
You will sometimes see the TRUNC function used as an alternative to the INT function. Like the INT function, the TRUNC function also removes the decimal portion of a number. Unlike INT, the TRUNC function doesn’t round, it truncates a number. In practice, the result is the same with timestamps: Although the TRUNC function and the INT function behave differently with negative numbers, this difference doesn’t affect dates which are by definition positive numbers in Excel. So, in practice, there is no difference between INT and TRUNC in this particular case.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
