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 2012/6/27 12:23, and you don’t want to retain the time stamp and want to remove the time (12:23) from the date. How could you quickly remove time from date in mulitple cells in Excel?
Recommended Productivity SoftwareOffice Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).
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 Aall of the time in selection have been deleted and the date format also have been set.
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- 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...