Download Article
Step-by-step guide to hide or remove the time from a date stamp in Excel
Download Article
Removing the time from a date value in Excel is easy, and there are plenty of ways to do it! You can format the cell to show only the date, preserving the time information. Or, you can remove the time entirely from the date value. This wikiHow shows you how to remove the time from a date in Microsoft Excel.
Things You Should Know
- Change the format of the cells to hide the time while maintaining the time value.
- Use INT or DATEVALUE to remove the time from a date.
- Alternatively, use Find and Replace or Text to Columns to remove the time.
Steps
-
Select the cell range containing the dates. This method changes the date format to hide the time portion. The time data will still be in the cell after changing the format. [1] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click Home . This tab is at the top of Excel.Advertisement
-
Click the down arrow next to the current format. This is in the “Number” section of the Home tab.
-
Click More Number Formats . This will open the “Format Cells” window.
-
Click Date . This is an option in the “Category” options on the left side of the window. [2] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click a date format in the “Type” section. Select a format that doesn’t contain the time. For example, 3/14/2012 or March 14, 2012 . [3] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click OK . This will confirm the format change. The selected cells should now be formatted without the time.
- For more Excel date info, check out our general guide on how to set a date in Excel .
Advertisement
-
Select the cell range containing the dates. This method uses the “Text to Columns” feature to split the time from the date into two separate columns.
- Note that this only works if your date and time are separated by a delimiter like a space. For example, “1/8/23 5:34 PM” has a space between the date and the time.
- Since Excel stores date information as a serial number, removing the time from a date sets the date’s time to 12:00 AM. You cannot completely remove the time aspect from a date value.
-
Click Data . This is a tab at the top of Excel. [4] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click Text to Columns . This will open the “Convert Text to Columns Wizard” window. [5] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Select Delimited . This is an option under “Choose the file type that best describes your data:”. [6] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click Next > . This will take you to Step 2 of the wizard. [7] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Select only Space in the “Delimiters” box.
-
Click Next > . This will take you to Step 3 of the wizard.
-
Enter the column settings (optional). You can click each new column in the “Data preview” box at the bottom of the wizard window. This will allow you to change the format or skip the column completely.
- You can also change where the columns will be placed using the “Destination” box.
-
Click Finish . This will place the new column data at the specified cell destination. [8] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source The dates column won’t show the time by default, but they are now all set to 12:00 AM due to the way Excel stores date and time information.
- For example, if you had the time “1/8/23 5:34 PM”, it would be split into three columns containing “1/8/23”, “5:34:00”, and “PM”.
Advertisement
-
Select the cell range containing the dates. This method uses the Find and Replace feature to replace the time portion of the dates with a blank. [9] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- Note that this only works if your date and time are separated by a space. For example, “1/8/23 5:34 PM” has a space between the date and the time.
- Since Excel stores date information as a serial number, removing the time from a date sets the date’s time to 12:00 AM. You cannot completely remove the time aspect from a date value.
-
Click Home . This tab is at the top of Excel. [10] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click Find & Select . This button is in the “Editing” section of the Home tab. [11] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click Replace . This is an option in the drop down menu.
-
Type a space and * into the “Find what” box. This tells Excel to look for text where there is a space followed by any number of characters.
-
Make sure the “Replace with” box is blank. This means Excel will replace the space and all of the characters after it with a blank.
-
Click Replace All . This will delete all of the times from the selected dates. The dates will now have the time 12:00 AM. If needed, you can change the cell format to remove the time. [12] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
Advertisement
-
Click the cell where you want the new date. Excel stores dates as a serial number that describes the date and time. The time portion is a decimal value at the end of the date’s whole number. This method uses the INT function to remove the time’s decimal portion from the date.
- Since Excel stores date information as a serial number, removing the time from a date sets the date’s time to 12:00 AM. You cannot completely remove the time aspect from a date value.
-
Type =INT(A1) in the cell. Replace “A1” with the cell reference for the original date when you type the formula . [13] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Press ↵ Enter . This will confirm the formula. You’ll see the date followed by “12:00 AM”. If needed, you can change the cell format to remove the time.
- You can copy and paste the formula as needed to remove the time from other dates in your dataset.
Advertisement
-
Click the cell where you want the new date. This method removes the time from a date using the DATEVALUE and TEXT function. [14] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source [15] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- Since Excel stores date information as a serial number, removing the time from a date sets the date’s time to 12:00 AM. You cannot completely remove the time aspect from a date value.
-
Type =DATEVALUE(TEXT(A1,"MM/DD/YY")) in the cell. Change A1 to the cell containing the original date. Since the DATEVALUE function creates a date value using a date written as text, you’ll need to convert the original date to text using TEXT. Specifying the "MM/DD/YY" format means DATEVALUE won’t get the time from the original date.
-
Press ↵ Enter . This will confirm the formula. You’ll see the date as a serial number. You can change the cell format to show the date in your preferred format.
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Tips
- For more helpful Excel info, check out how to use the SUM function and how to write an IF-THEN formula .Thanks
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
Advertisement
References
- ↑ https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
- ↑ https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
- ↑ https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
- ↑ https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
- ↑ https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
- ↑ https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
- ↑ https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
- ↑ https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
- ↑ https://support.microsoft.com/en-us/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90
- ↑ https://support.microsoft.com/en-us/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90
- ↑ https://support.microsoft.com/en-us/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90
- ↑ https://support.microsoft.com/en-us/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90
- ↑ https://support.microsoft.com/en-us/office/int-function-a6c4af9e-356d-4369-ab6a-cb1fd9d343ef
- ↑ https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252
- ↑ https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
About This Article
Thanks to all authors for creating a page that has been read 36,571 times.
Advertisement
