How to remove time from date in Excel?
Sometimes, when you import data into Excel, if there has a column of date with time stamp, such as 2/17/2012 12:23, and you don’t want to retain the time stamp and want to remove the time 12:23 from the date and only leave the date 2/17/2012. How could you quickly remove time from date in mulitple cells in Excel?
Remove time from date with Format Cells
Remove time from date with Find and Replace function
Remove time from date with VBA code
Remove time from date with Kutools for Excel's Remove time from date function (best) 
If you want to follow along with this tutorial, please download the example spreadsheet.
Remove time from date with Format Cells
1.Select the cells you want to remove time, and right click to show the context menu, and choose Format Cells. See screenshot:
2. In the Format Cells dialog, select Date from the Category list, and select one type of date as you need form right section. See screenshot:
3. Click OK, now the time has been removed from each date cell. See screenshot:
|
Remove Time From DateTime |
|
In Excel, to remove 12:11:31 from 1/21/2017 12:11:31 and make it exactly 1/21/2017, you may have to take some time to create a formula to handle this job. However, the Remove time from date utility of Kutools for Excel can quickly remove timestamp permanently from the date time formatting in Excel. Click to download 30-day free trial. |
 |
Remove time from date with Find and Replace function
1. Select the date range that you want to remove the time.
2. Click Home > Find & Select > Replace, and a Find and Replace dialog box will pop out. See screeenshot:
3. Then enter a spacebar and a asterisk * into the Find what box. And leave blank in the Replace with box.
4. Click Replace All. And all of the time stamp have been removed in the date range. See screenshot:
5. Then you can format the range to show only the date.
Remove time from date with VBA code
The following VBA code can help you quickly to remove the time stamps from the date directly. Do as follows:
1. Select the range that you want to remove the time.
2. Click Developer>Visual Basic or press Alt + F11, a new Microsoft Visual Basic for applications window will be displayed, click Insert>Module, and input the following code into the Module:
VBA: Remove time from date.
Sub ConvertDates()
'Updateby20140529
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)
Next
WorkRng.NumberFormat = "mm/dd/yyyy"
End Sub3. Then click 
button to run the code. And a dialog pops out for selecting a range to remove the time from date. See screenshot:
4. Click OK, and all of the time in selection have been deleted and the date format also have been set.
Remove time from date with Kutools for Excel's Remove time from date function (best)
Actually, if you have Kutools for Excel -- a handy tool with more than 300 functions, you can apply its Remove time from date function to quickly and directly remove time from the datetime cell.
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. | ||
After free installing Kutools for Excel, please do as below:
1. Select a cell that you want to place the date, and click Kutools > Formula Helper > Formula Helper. See screenshot:
2. In the Formulas Helper dialog, choose Date from the drop-down list of Formula Type, then click at Remove time from date in the Choose a formula section, then go to the Argument input section, select a cell that you want to remove time from. See screenshot:
3. Click Ok, the time has been remove from the datetime, and then to drag fill handle over the cells you want to apply this fomrula as you need , see screenshot:
Quickly and easily convert date to other date formatting in Excel
|
| The Apply Date Formatting of Kutools for Excel can quickly convert a standard date to the date formatting as you need as, such as only display month, day, or year, date format in yyyy-mm-dd, yyyy.mm.dd and so on. click for full- featured free trail in 30 days! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Related Articals:
How to remove year from date in Excel?
How to change multiple dates to day of week in excel?
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
