When using Excel, we will often find ourselves in a situation when we have to manipulate data. More often than not, these data will be in form of a date.
One of the situations that can be tricky is that if we have a full date and time in our cell, but we do not want to show the exact time, rather just the date.
There are several ways in which we could remove the time and leave only the date in our cells.
Remove the Time with Format
Let us suppose that we have the table with three rows that have date and time:
First, let’s copy and paste the data into column B and then select the B2:B4 range. Go to Home >> Number >> Number Formats >> More Number Formats:
Once we click on it, we will have an opened window from which we will choose the Number tab and then go to Date. Select the date that best suits us. We have selected the first in the list in our case.
Click OK, and now we have only the date in our desired range, without the time.
It is important to note that in this case, we didn’t delete the time. We just hid it, so if we use these cells for calculations of any kind, time will be included.
We could, of course, just change the format in column A, but we wanted to keep the original preview to show the difference better.
Remove the Time with Int Formula
Now, if we want to remove time completely from our cells, we can use several formulas and methods. The first one on the list is the INT function.
The Excel INT returns the integer part of a decimal number by rounding it down to the integer.
It’s important to know that date and time are stored as numbers in Excel. The date is stored as plain numbers: for example, 44371 (this is equal to January 24th, 2021) while the time has a decimal value as well (44371.027 is 12:40 AM of the above-stated date).
The integer part stands for the date while the decimal part stands for the time. So, logically, if we use the INT function, we will retrieve the only date from our cells.
Our formula in cell C2 will be:
We will expand this formula to cells C3 and C4 and we will have the following results:
The only issue is that we still see the time part in our results. The reason behind this is the format of the cell, which is forcing it to show the date as well as the time. When the value is 0, the time will always show 12:00 AM for all the cells.
To get rid of this part, we have to format our cells. To do this easier, we will go to the Home tab and then on Number Formatting. There, we will simply select Short Date or Long Date.
The time has now successfully been removed from column C.
Remove the Time with Datevalue and Text
There is also one more function that can be used to derive only the date from our data. This function is called DATEVALUE. This function has only one parameter, and that is date_text.
We can immediately see that, for our date to be derived from column A, we need to have a text format of our date. Otherwise, we will get an error message:
So, to return our date as a text, we will have to combine our DATEVALUE function with TEXT. This function has the following syntax:
The value will be the cell with the date, and our format has to be in the format of our date (MM-DD-YYYY).
Notice that we have a month in the first place, just like in our date. If we format our date as DD-MM-YYYY, for cells in column A, we would get an error message.
Once we input this formula in cell D2, we will get the result as follows:
All we have to do now is make it so that the TEXT function is the parameter for our DATEVALUE function. We will just place the DATEVALUE function in front of the TEXT function:
You will notice that this function retrieves numeric value. We have already explained that dates are nothing but numbers for Excel. We can change the format of this cell to be the Short or Long Date, as in previous examples.
Remove the Time with Text to Columns
One very convenient way to manipulate the cells, i.e. to split the existing text in our cell is to use Text to Columns. We select A2:24 range, then go to Data >> Data Tools >> Text to Columns.
Once we click on it, we will be presented with the Convert Text to Columns Wizard that has three steps.
In the first step, we will choose Delimited as the type that best describes our data:
Then we will click Next and go to Step 2. Since the data in our cell is separated by space, we will select Space as our Delimiter.
In the final step, we have to exclude the time and AM from our final data. We will click on the second and third parts of our text and choose the Do not import column (skip). This will make sure that we only keep our date in the A column, but not the time.
We will, however, be shown with the time in the cells in the A column. We will see generic (12 AM) time due to the format of the cells.
All we have to do to fix this is to change the format of the cells in the A column to be either Short or Long Date.
Remove the Time with Find and Replace
We can always call Find and Replace to help us remove the time from time and date.
In our E column, we have our original values. We will select range E2:E4, go to Home >> Editing >> Find & Select >> Replace.
We will be presented with Find and Replace window in which we will input space and asterisk sign (*).
This will make sure that all the text after the space in our cells gets deleted. Since this text is the time in our cells, this will leave us only with the dates once we click Replace All.
Once again, we will be shown with the time in our data. We will see generic (12 AM) time due to the format of the cells.
As in the examples above, we format the cells in the E column to be either Short or Long date.