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 Text to Columns function

Remove time from date with VBA code

Remove time from date with Kutools for Excel's Remove time from date function (best) good idea3


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.
doc kte remove time from date

arrow blue right bubbleRemove 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:
doc remove time from datetime 1

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:
doc remove time from datetime 2

3. Click OK, now the time has been removed from each date cell. See screenshot:
doc remove time from datetime 3


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 all of the time in selection have been deleted and the date format also have been set.
doc-remove-time-from-date6


arrow blue right bubbleRemove 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 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 HelperRemove time from date. See screenshot:
doc kte remove time from date 1

2. In the Formula Helper dialog, select  the cell which contains the datetime into the textbox, and click Ok. See screenshot:
doc kte remove time from date 2

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:

doc kte remove time from date 3 doc kte remove time from date 4

Tip. If you want to extract some string or from a cell, please go to apply Kutools for Excel's Extract Text function as below screenshot shown, It’s full function without limitation in 60 days, please download and have a free trial now.

doc remove time from datetime 5 doc remove time from datetime 6
doc extract 1  

Related Articals:

How to remove year from date in Excel?

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


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 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...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink +5 Workhard
Thanks a bunch, this really helped me out today!!!
2014-01-22 11:24 Reply Reply with quote Quote
Permalink +5 Gina
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.
2014-01-30 15:42 Reply Reply with quote Quote
Permalink +1 MOHAMMED MOINUDDIN O
Excellent Tip!!!! I was trying to get right thru various ways, but this is the best!!!!
2014-02-21 06:23 Reply Reply with quote Quote
Permalink +1 Murali
Ton of Thanks! Excellent Tips... :D
2014-03-07 12:02 Reply Reply with quote Quote
Permalink 0 Vbernard
Great! Finally found something that works! Saved me tons of time! :lol:
2014-04-04 15:54 Reply Reply with quote Quote
Permalink 0 Nagaraj
Thanks. Very much useful & easy.
2014-04-09 05:32 Reply Reply with quote Quote
Permalink -1 Erik L
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()
2014-04-15 22:05 Reply Reply with quote Quote
Permalink +3 JP Laroche
That overkill.

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

The date value in Excel is a number of day.
2014-05-05 17:27 Reply Reply with quote Quote
Permalink 0 Blakey L
This doesn't work if the time is afternoon midday. If it's in the afternoon, the function will round the date up to the next day.
2015-03-09 09:56 Reply Reply with quote Quote
Permalink 0 LiorO
you can use the floor function instead.
2015-03-15 10:09 Reply Reply with quote Quote
Permalink 0 tom
I did rounddown(A2,) worked like a charm; thanks though for everything else guys!!!
2015-05-20 17:06 Reply Reply with quote Quote
Permalink 0 davidplayboy
txs alot it relly helped me>>>about if i want to remove date from time in Excel
2014-06-06 13:07 Reply Reply with quote Quote
Permalink 0 Vikram
We stumble over pebbles and never over mountains!! such a beautifully simple solution (the space * combo)

Thank you Thank you and GOD bless

Cheers
2014-06-19 12:11 Reply Reply with quote Quote
Permalink 0 Anonymus
This is a lie do not use
2014-07-18 10:22 Reply Reply with quote Quote
Permalink 0 Karen
There's one more method available . . . ctrl+F in the Replace Tab, enter * (without the brackets) . . . time disappears

grins
2014-09-11 11:55 Reply Reply with quote Quote
Permalink 0 Sadegh
Thanks, Very helpful
2015-01-15 15:00 Reply Reply with quote Quote
Permalink 0 Steph
Awesome, easy solution!! Thanks!
2015-01-20 19:05 Reply Reply with quote Quote
Permalink 0 Amar
useful information..
2015-01-22 06:53 Reply Reply with quote Quote
Permalink 0 Michael leng
Thanks for the delimited function. I can use it for my work.

EXCELTIP2DAY
2015-06-02 04:35 Reply Reply with quote Quote
Permalink 0 Parveen Kumar
Part Received Fully At HCL Drop Point-(All Parts Received) at 2015-12-10 15:55:00
how can remove all text
showing only date
2015-12-10 12:12 Reply Reply with quote Quote
Permalink 0 Robert Carter
dropping the time with a global replace - GENIUS, dude (...or dudette).
2016-10-20 11:25 Reply Reply with quote Quote
Permalink 0 Ashfaq Ahmed
just use formula
=int(date)
2016-10-20 14:04 Reply Reply with quote Quote
Permalink 0 Sam
Thank you! This helped me a great deal to make dates so that I can then create charts showing accurate trends in performance.
2016-11-20 01:28 Reply Reply with quote Quote
Permalink 0 anil
Thanks you so much , for that replace option work,,thank you
2017-02-15 16:26 Reply Reply with quote Quote

Add comment


Security code
Refresh