totn Excel

MS Excel 2003: Convert date/time value to a date value (remove time portion)

Question: In Microsoft Excel 2003/XP/2000/97, how do I convert a column with a date format to date only, no time?

Answer: You can remove the time portion from a date by using the YEAR function, MONTH function, and DAY function.

We'll demonstrate with the example below:

In this example, we have a date/time value in cell A1 and we'd like to remove the time portion from this value.

Microsoft Excel

In cell B1, we've entered the following formula that uses the MONTH, DAY and YEAR functions:

=MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)

The order of how you use the Year, Month, and Day functions will depend on how you're formatting your date.

In this example, we've formatted cell B2 with the following format:

Microsoft Excel

You may need to reorder the Year, Month, and Day functions based on your Date format selected in Excel.

Frequently Asked Questions

Question: I tried your solution above:

=MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)

But Excel did not recognize the field as a date field. When I try to sort the dates in either ascending or descending order, the values are not sorted as dates, but rather as text values. How can I get Excel to recognize the values as dates so that I can perform a proper sort?

Answer:In order for Excel to recognize the values as dates, you will need to modify your formula as follows:

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1))

The DATEVALUE function returns the serial number of a date, allowing you to sort your dates properly.

Share on: