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 it from the time (12:23) from the date. How could you quickly remove time from date in mulitple cells in Excel?

Remove time from date with Find and Replace function

Remove time from date with Text to Columns function

Remove time from date with VBA code

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

arrow blue right bubbleRemove 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:

doc-remove-time-from-date1

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:

doc-remove-time-from-date2

5. Then you can format the range to show only the date.


arrow blue right bubbleRemove time from date with Text to Columns function

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:

doc-remove-time-from-date3

3. Check Delimited, and click Next. Then check Space, and go on next.

doc-remove-time-from-date4

4. At last, click Finish. And the time have been isolated from the date. See screenshot:

doc-remove-time-from-date5

5. Then you can delete column D. And remember to format the date as you like.


arrow blue right bubbleRemove 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 Sub

3. Then click doc-multiply-calculation-3 button to run the code. And a dialog pops out for selecting a range to remove the time from date. See screenshot:

doc-remove-time-from-date7

4. Click OK, and Aall of the time in selection have been deleted and the date format also have been set.

doc-remove-time-from-date6


Related Articals:

How to remove year from date in Excel?

How to change multiple dates to day of week in excel?

Convert date to weekday name or month name in Excel?

How to change American date format in Excel 2007/2010?


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+2#Workhard2014-01-22 11:24
Thanks a bunch, this really helped me out today!!!
Reply | Reply with quote | Quote
+4#Gina2014-01-30 15:42
So simple! I kept looking for DATE/TIME formulas and couldn't figure this out. SO, finally I googled it, and this was the first link that showed up. Thanks!!! You saved me a ton of work.
Reply | Reply with quote | Quote
+1#MOHAMMED MOINUDDIN O2014-02-21 06:23
Excellent Tip!!!! I was trying to get right thru various ways, but this is the best!!!!
Reply | Reply with quote | Quote
+1#Murali2014-03-07 12:02
Ton of Thanks! Excellent Tips... :D
Reply | Reply with quote | Quote
0#Vbernard2014-04-04 15:54
Great! Finally found something that works! Saved me tons of time! :lol:
Reply | Reply with quote | Quote
0#Nagaraj2014-04-09 05:32
Thanks. Very much useful & easy.
Reply | Reply with quote | Quote
-1#Erik L2014-04-15 22:05
This solution only works if all your users have the date format of "mm/dd/yyyy" as defualt. A more generalized solution is to use the function DATEVALUE()
Reply | Reply with quote | Quote
+1#JP Laroche2014-05-05 17:27
That overkill.

Just round the thing to the decimal.
=round(A1,0)

The date value in Excel is a number of day.
Reply | Reply with quote | Quote
0#davidplayboy2014-06-06 13:07
txs alot it relly helped me>>>about if i want to remove date from time in Excel
Reply | Reply with quote | Quote
0#Vikram2014-06-19 12:11
We stumble over pebbles and never over mountains!! such a beautifully simple solution (the space * combo)

Thank you Thank you and GOD bless

Cheers
Reply | Reply with quote | Quote
0#Anonymus2014-07-18 10:22
This is a lie do not use
Reply | Reply with quote | Quote

Add comment


Security code
Refresh