Table of Contents
Converting a datetime to a date in Excel is a simple process that can be done using the built-in functions and formulas within the program. A datetime is a combination of both date and time, while a date only includes the month, day, and year. By converting a datetime to a date, you can remove the time component and work with the date portion of the data. This can be useful for organizing and analyzing data in a more simplified manner. To convert a datetime to a date in Excel, you can use the DATE function or the Text to Columns feature. These methods allow you to select the datetime data and extract only the date portion, converting it into a date format that can be easily manipulated and analyzed. By following these steps, you can effectively convert a datetime to a date in Excel and streamline your data analysis process.
Convert Datetime to Date in Excel
You can use the following syntax to convert a datetime to a date in Excel:
=TEXT(A2,"m/d/yyyy")
This particular formula converts the datetime in cell A2 to only a date.
For example, if cell A2 contains 1/1/2023 2:14 AM then this formula would return 1/1/2023.
The following example shows how to use this formula in practice.
Example: Convert Datetime to Date in Excel
Suppose we have the following column of datetimes in Excel:
Suppose we would like to convert each datetime in column A to only a date.
To do so, we can type the following formula into cell B2:
=TEXT(A2,"m/d/yyyy")
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays each datetime from column A as only a date.
How This Formula Works
Recall the formula that we typed into cell B2 to convert the datetime in cell A2 to a date:
=TEXT(A2,"m/d/yyyy")
In this formula, we used the format code of m/d/yyyy which tells Excel to display the month (m = month as 1 or 2 digits), day (d = day as 1 or 2 digits) and year (yyyy = year as 4 digits) with slashes in between them.
The end result is that we’re able to display only the portion of the datetime that contains the date.
The time portion of the datetime is simply not shown at all.
Note: You can find the complete documentation for the TEXT function in Excel .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: