Remove time from timestamp

In this example, the goal is to use a formula to remove the time value from a timestamp that includes both the date and time. To solve this problem, it's important to understand that Excel handles dates and time using a scheme in which dates are large serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date portion and .5 is the time portion. This means the main task in this problem is to remove the decimal portion of the number.

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:

dd-mmm-yyyy

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:

=INT(10.8) // returns 10

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):

=INT(A1)
=INT(36678.5)
=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:

=TRUNC(A1)
=TRUNC(36678.5)
=36678

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.