Summary

To extract the date part of a date that contains time (i.e. a datetime), you can use the INT function. In the example shown, the formula in cell D5 is:

=INT(B5)

Generic formula

=INT(date)

Explanation 

Excel handles dates and time using a scheme in which dates are 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.

If you have dates that include time, you can use the INT function to extract just the date part. The INT function returns the integer portion of a number that includes a decimal value. 

So, assuming 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)

The time portion of the value (the fractional part) is discarded. To see the result 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.

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.