Exceljet

Quick, clean, and to the point

Extract date from a date and time

Excel formula: Extract date from a date and time
Generic formula 
=INT(date)
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)
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.

Author 
Dave Bruns
Related formulas 
Excel formula: Extract time from a date and time
Extract time from a date and time
Excel handles dates and times using a system 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 (June 1, 2000) and .5 is the...
Related functions 
Excel INT function
Excel INT Function

The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note the INT function rounds down, so negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns...

Excel TRUNC function
Excel TRUNC Function

The Excel TRUNC function returns a truncated number based on an (optional) number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified.

Good links 
http://excelsemipro.com/2013/02/extracting-integers-and-fractions-in-microsoft-e…
Related courses 
Core Formula

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.