tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities DigMinecraft
Share this page:

MS Excel: Convert date/time value to a date value (remove time portion) in Excel 2003/XP/2000/97

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:

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