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 datetime
|With Kutools for Excel's Formula Helper utility, you can quickly solve some complex jobs as quickly as you can. For example, you can quickly remove time from datetime cell with the Remove time from date.|
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:
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.
With the function of Text to Columns, you can also remove time from the date. Please do as the following steps:
1. Highlight the range you need to delete the time stamps.
2. Click Data > Text to Columns, and a Convert Text to Columns Wizard will appear. See screenshot
3. Check Delimited, and click Next. Then check Space, and go on next.
4. At last, click Finish. And the time have been isolated from the date. See screenshot:
5. Then you can delete column D. And remember to format the date as you like.
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 Sub
3. 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.
Actually, if you have Kutools for Excel -- a handy tool with more than 120 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 120 handy functions, makes your jobs easier.|
After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)
1. Select a cell that you want to place the date, and click Kutools > Formula Helper > Remove time from date. See screenshot:
2. In the Formula Helper dialog, select the cell which contains the datetime into the textbox, and click Ok. 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:
|Kutools for Excel: 200 + functions you must have in Excel, 60-day free trial from here|
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 months agoThanks you so much , for that replace option work,,thank you
To post as a guest, your comment is unpublished.· 9 months agoThank you! This helped me a great deal to make dates so that I can then create charts showing accurate trends in performance.
To post as a guest, your comment is unpublished.· 10 months agojust use formula
To post as a guest, your comment is unpublished.· 10 months agodropping the time with a global replace - GENIUS, dude (...or dudette).
To post as a guest, your comment is unpublished.· 1 years agoPart Received Fully At HCL Drop Point-(All Parts Received) at 2015-12-10 15:55:00
how can remove all text
showing only date