Table of Contents
Yes, you can remove time from date in Excel by using the Text to Columns feature to separate the date and time and then delete the time cells. You can also use the TIMEVALUE function to convert the time portion of the date into a decimal number and then subtract it from the date. This will leave you with a date without the time.
You can use the following formula to remove the time from a date with a time in Excel:
=TEXT(DATEVALUE(TEXT(A2,"mm/dd/yyyy")),"mm/dd/yyyy")
This particular formula will remove the time from the date and time in cell A2.
For example, if cell A2 contains 1/5/2023 6:12 AM then this formula will convert it to 1/5/2023.
The following example shows how to use this formula in practice.
Example: Remove Time from Date in Excel
Suppose we have the following list of date and times in Excel:
To remove the time from the cell in A2, we can type the following formula into cell B2:
=TEXT(DATEVALUE(TEXT(A2,"mm/dd/yyyy")),"mm/dd/yyyy")
We can then drag and fill this formula down to each remaining cell in column B:
Notice that column B now displays each date in column A without the time.
Also note that the formula mm/dd/yyy tells Excel to display each month with two digits, e.g. display 01 instead of 1.
If you would only like to display one digit for the month, you can instead use m/dd/yyy as follows:
=TEXT(DATEVALUE(TEXT(A2,"mm/dd/yyyy")),"m/dd/yyyy")
The following screenshot shows how to use this formula in practice:
Notice that column B now only displays each month with one digit.
Note: Months that have two digits will still be displayed with two digits.