How to Easily Remove the Time from a Date in Microsoft Excel

This article was co-authored by wikiHow staff writer, Kyle Smith. Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.

This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.

This article has been viewed 32,420 times.

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.

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

Changing the Date Format

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 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." width="460" height="348" />

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

• For more Excel date info, check out our general guide on how to set a date in Excel.

Using Text to 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

• You can also change where the columns will be placed using the “Destination” box.

• 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”.

Using Find and Replace

• 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

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

Using the INT Function

• 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

• You can copy and paste the formula as needed to remove the time from other dates in your dataset.

Using the DATEVALUE Function

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

Expert Q&A

You Might Also Like

How to Merge Cells in Microsoft Excel: A Quick Guide

How to Password Protect a Microsoft Excel Spreadsheet

How to Use If‐Else in Microsoft Excel: Step-by-Step Tutorial

How to Show Hidden Columns in Microsoft Excel & Unhide Column A

How to Insert and Delete Rows in Microsoft Excel: Quick Guide

4 Easy Ways to Add Links in Microsoft Excel

How to Fix Ethernet Doesn't Have a Valid IP Configuration on Windows

How to Create an Inventory List in Microsoft Excel: Step-by-Step Guide