To remove time from a timestamp (or date) that includes both date and time, you can use the INT function. In the example shown, the formula in cell D5 copied down, is:


The result in column D is the date only from the timestamps in column B. The time in the original timestamps is discarded.

Generic formula



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:


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


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:

Same dates with General number format applied

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 Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.