Yeah, we’ve all pretty much seen timestamps in excel spreadsheets. In some cases, you might not need the time in the set of data. You might need the dates only. In this article, we’ll be discussing how to remove time from date in excel.
Extracting the time from the date might seem a bit complicated at first, but Excel does have some neat tricks that can help you do just that. Wondering how? Well then, you’ve hit the jackpot. We’re about to cover all the possible ways to complete the task.
So, if extracting time from date is in your agenda, you won’t be disappointed. Enough with the introductions, let’s start things off straight away.
7 Easy Ways to Remove Time from Date in Excel
You might be thinking, “Do I really need this many ways to do this simple task?” Well, the answer is no, you don’t. Just choose the method to your liking and the ones that are best suited for the type of work you’re involved in.
Firstly, we’ll be discussing three formulas that can be applied to complete the task, then we’ll move forward to the other procedures.
The INT Function
What the INT function basically does is, it rounds a number to the closest possible integer. Confused about how it can actually get the job done? Let me explain.
- In this case, we have to use the INT function with only one argument. In this case, a cell reference to a timestamp in order to round the timestamp to the nearest possible integer.
- To explain the role of the integers, we might have to demonstrate something. Notice the image below. Here we have copied the value of A2 and B2 and placed it elsewhere. This has displayed a couple of numbers as the following.
- You might be curious about why the date is stored as serial numbers and the time is stored as decimals in the excel sheet. Again, if you change the cell format of any date to General, you’ll again see a certain number.
- Here, 44857 is the integer of the date 23/10/2022, and .5145833333 is representing the time 12:21.
- You must have noticed that the INT function has rounded off the number 44857.5145833333 to the nearest integer which is 44857 and left the time as 0.
- Now all that’s left to do is, changing the result cell from custom format to date format.
- Once you’ve done that, you’ll see the time disappear.
- And that’s pretty much how you use the INT function to remove time from date in your Excel sheet.
The DATEVALUE Function
This time we’re going to work in the B3 Cell. Here’s how we’re going to apply the DATEVALUE function.
- Firstly, you’ll want to place this formula on the formula bar of cell B3: =DATEVALUE(A3)
- What the DATEVALUE function does is, it takes the values of the dates in a particular cell only, discarding the time value.
- So, once you press enter and apply it, the results should look very much like this.
- Here, you can see the time values have been changed to zero.
- Now all you have to do is, set the cell format from Custom to Date.
- And thus, you’ll get the results you’ve been looking for. The time will be extracted from the date. It should look something like this.
Note: You can use the TRUNC function instead of INT. The only difference is that this function will truncate the values. However, the results should be the same.
The TEXT Function
What the TEXT function does is, it converts a value to text in a certain number format. Using this, you can change the timestamp to date only. Let’s see how it works.
- What the TEXT function does is, it picks up the data from the designated cell as the format supplied in the formula bar.
- In this case, the formula should look like this: =TEXT(A4,”dd/mm/yyyy”). Since we’ll be trying to get our results in cell B4, the cell number in the formula should be A4.
- Once you press enter, the results should look pretty much like this.
- And there you have it; the time has been removed from the timestamp without having to format the cell.
The Find and Replace Method
You can easily use the Find and Replace method along with date formatting to achieve the desired results. Just follow the steps below.
- The first thing you want to do here is select the time stamps you want to remove the time from.
- In this case, we’re going to choose cell A5, A6, and A7.
- Once you’re done, choose the find and select option on the Home tab, or simply press Ctrl + H.
- On the Find What box type in space character, and asterisk “*”.
- Then, click on Replace All. A popup window will let you know of the replacements made. Press Ok.
- The results should look like the image below.
- As you can see, the selected three cells have the time value changed to zero.
- The next step would be to simply change the cell format from Custom to Date.
- And that’s about it. You’ll get your desired results instantly.
The Text to Column Method
This process basically nullifies the value of the time to zero. Just like the few previous method, this also requires cell formatting in the end. Let’s see how to apply the method.
- The first thing you want to do is, go to the Data tab and select the Text to Column option.
- In the popup window, select the Fixed Width option, and click on Next.
- Now, move the vertical line between the date and time data.
- Click on Next again.
- Now that you’ve done it, select the column with time. It should turn black once you’ve done it.
- Check the Do not import column option and click on finish.
- Finally, the time value should be nullified to zero.
- Now, just change the cell format from Custom to Date, and you’re done.
The Format Cells Method
This method is really easy to execute. Just follow the simple procedure below.
- Select the cells you want to remove time from the date.
- Go to the Number group under the Home tab.
- Select Short Date and you’ll find the time removed from the sheet. Yet, it will remain on the formula bar.
The VBA Method
To get things done really fast, you can use the VBA method. Just follow the steps.
- Just select the range of data that you want to remove the time from.
- By bringing up the VBA window by pressing Alt + F11, you’ll have to click on module in the insert tab.
- There, just insert the following code.
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = “KutoolsforExcel”
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.Int(Rng.Value)
WorkRng.NumberFormat = “mm/dd/yyyy”
- Run the code, and click the Ok button. That should do the trick instantly.
After going through the whole article, I believe you’ve picked up a trick or two on how to remove time from date in your Excel worksheet. Some might seem a tad bit complicated from the others but every single one of them is doable in the end.
Go ahead and try the methods out for yourself. You’ll never know which one is best for you till you have a more hands on experience.
Let’s close things up for now. I hope you enjoyed the read.