How to extract date from datetime in excel

Answer

A datetime value in Excel contains both a date and a time component. Extracting only the date is possible through formulas, formatting, or functions, depending on your requirements.

1. Using the INT Function

The INT function removes the decimal portion of a number. In Excel, dates are stored as whole numbers and times as fractional numbers.

Steps:

1. Suppose the datetime is in cell A1.

2. Enter the formula:

   =INT(A1)

3. Press Enter. The result will display the date without the time.

4. Format the cell as a date if needed:

   • Go to Home → Number → Short Date or Long Date.

2. Using the DATE Function with YEAR, MONTH, and DAY

This method reconstructs the date explicitly.

Steps:

1. Suppose the datetime is in cell A1.

2. Enter the formula:

   =DATE(YEAR(A1), MONTH(A1), DAY(A1))

3. Press Enter. The formula returns the date only, ignoring the time component.

3. Changing the Cell Format to Date Only

Formatting the cell does not remove the time value but displays only the date.

Steps:

1. Select the cell or range containing datetime values.

2. Right-click and choose Format Cells.

3. In the Number tab, select Date.

4. Pick the desired date format and click OK.

4. Using TEXT Function to Convert to Date String

The TEXT function converts datetime to a date as text.

Steps:

1. Suppose the datetime is in cell A1.

2. Enter the formula:

   =TEXT(A1, "mm/dd/yyyy")

3. Press Enter. The result will show the date as text.

Note: Using TEXT converts the date to text, so arithmetic operations like addition or subtraction will not work unless converted back to a date.

5. Using Flash Fill (Excel 2013 and Later)

Flash Fill can automatically extract patterns like dates from datetime entries.

Steps:

1. In a column next to the datetime values, type the date corresponding to the first datetime.

2. Go to Data → Flash Fill or press Ctrl + E.

3. Excel fills the rest of the column with dates extracted from the datetime values.

Key Notes

• The INT and DATE functions preserve the value as a proper Excel date.

• Formatting changes only the display without altering the underlying value.

• TEXT is useful for display or exporting purposes.

This ensures correct extraction and usage in calculations like age, duration, or comparisons.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report