Table of Contents
Removing the time from a date in Excel can be achieved by using the “Format Cells” feature. By selecting the cells containing the dates, you can choose the “Custom” category and then select the desired date format without the time. This will remove the time component from the date and display it in the chosen format. Alternatively, you can also use the “Text to Columns” feature to split the date and time into two separate columns, and then delete the time column. This will result in the date being displayed without the time in its own column. Using either method, you can easily remove the time from a date in Excel.
Remove Time from Date in Excel (With Example)
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.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How do I remove the time from a date in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-remove-the-time-from-a-date-in-excel/
stats writer. "How do I remove the time from a date in Excel?." PSYCHOLOGICAL SCALES, 25 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-remove-the-time-from-a-date-in-excel/.
stats writer. "How do I remove the time from a date in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-remove-the-time-from-a-date-in-excel/.
stats writer (2024) 'How do I remove the time from a date in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-remove-the-time-from-a-date-in-excel/.
[1] stats writer, "How do I remove the time from a date in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I remove the time from a date in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.