Excel formula: Extract date from a date and time

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 with time values and you want to extract only the date portion, you can use a formula that uses the INT function. So, assuming A1 contains the date and time, June 1, 2000 12:00 PM, the formula below returns just the date portion (36678):

=INT(A1)

The time portion of the value (the fractional part) is discarded. You could also use the TRUNC function with the same result:

=TRUNC(A1)

Notes:

1. With either method above, make sure you use a date format on the result that does not include a time. Otherwise, you'll see the time displayed as 12:00 AM.

2. For dates and times (which must be positive in Excel) there is no difference in using INT and TRUNC to extract an integer. But INT actually rounds numbers down to the nearest integer, which makes a difference when values are negative. See the TRUNC link below for more details.

Author

Dave Bruns

Related formulas

Extract time from a date and time

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 and .5 is the time. If you have...

Related functions

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 -11.

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...

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.