Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

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.

doc-convert-date-unix-1

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 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 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

arrow blue right bubble Remove time from Date Time


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!
doc apply date format
 
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?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    John Wagner · 1 years ago
    Copy to a cell and format as general (MS Date Value), then ROUNDDOWN,0 Simple.
  • To post as a guest, your comment is unpublished.
    Ranek · 1 years ago
    what i've noticed is that when you clean the "date" column, you will always get a standard Number comprised of 5 digits. Usually it something like 43xxx. If the cell contains a timestamp ( 09:40:33) , your number will be 43xxx.yyy, where .yyy contains the details of the Hours:minutes:seconds. What always works a treat for me is to eliminate what's after the "." . To do this, I simply use =left(clean(A1),5) , whereby A1 = cell with the date stamp :) hope this helps anyone.
    • To post as a guest, your comment is unpublished.
      Gabriel · 11 months ago
      Thanks a lot! For me it worked. Is funny you can found better solutions in the comments section than in the actual post ;)
  • To post as a guest, your comment is unpublished.
    Brandon · 1 years ago
    useful, thanks
  • To post as a guest, your comment is unpublished.
    Gustavo Henrique · 1 years ago
    thank you !!!
  • To post as a guest, your comment is unpublished.
    Helio · 1 years ago
    cara show de bola
    muitissimo obrigado
  • To post as a guest, your comment is unpublished.
    anil · 2 years ago
    Thanks you so much , for that replace option work,,thank you
  • To post as a guest, your comment is unpublished.
    Sam · 2 years ago
    Thank 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.
    Ashfaq Ahmed · 3 years ago
    just use formula
    =int(date)
  • To post as a guest, your comment is unpublished.
    Robert Carter · 3 years ago
    dropping the time with a global replace - GENIUS, dude (...or dudette).
  • To post as a guest, your comment is unpublished.
    Parveen Kumar · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    Michael leng · 4 years ago
    Thanks for the delimited function. I can use it for my work.

    EXCELTIP2DAY
  • To post as a guest, your comment is unpublished.
    Amar · 4 years ago
    useful information..
  • To post as a guest, your comment is unpublished.
    Steph · 4 years ago
    Awesome, easy solution!! Thanks!
  • To post as a guest, your comment is unpublished.
    Sadegh · 4 years ago
    Thanks, Very helpful
  • To post as a guest, your comment is unpublished.
    Karen · 5 years ago
    There's one more method available . . . ctrl+F in the Replace Tab, enter * (without the brackets) . . . time disappears

    grins
  • To post as a guest, your comment is unpublished.
    Anonymus · 5 years ago
    This is a lie do not use
  • To post as a guest, your comment is unpublished.
    Vikram · 5 years ago
    We stumble over pebbles and never over mountains!! such a beautifully simple solution (the space * combo)

    Thank you Thank you and GOD bless

    Cheers
  • To post as a guest, your comment is unpublished.
    davidplayboy · 5 years ago
    txs alot it relly helped me>>>about if i want to remove date from time in Excel
  • To post as a guest, your comment is unpublished.
    JP Laroche · 5 years ago
    That overkill.

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

    The date value in Excel is a number of day.
    • To post as a guest, your comment is unpublished.
      Blakey L · 4 years ago
      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.
      • To post as a guest, your comment is unpublished.
        LiorO · 4 years ago
        you can use the floor function instead.
        • To post as a guest, your comment is unpublished.
          tom · 4 years ago
          I did rounddown(A2,) worked like a charm; thanks though for everything else guys!!!
  • To post as a guest, your comment is unpublished.
    Erik L · 5 years ago
    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()
  • To post as a guest, your comment is unpublished.
    Nagaraj · 5 years ago
    Thanks. Very much useful & easy.
  • To post as a guest, your comment is unpublished.
    Vbernard · 5 years ago
    Great! Finally found something that works! Saved me tons of time! :lol:
  • To post as a guest, your comment is unpublished.
    Murali · 5 years ago
    Ton of Thanks! Excellent Tips... :D
  • To post as a guest, your comment is unpublished.
    MOHAMMED MOINUDDIN O · 5 years ago
    Excellent Tip!!!! I was trying to get right thru various ways, but this is the best!!!!
  • To post as a guest, your comment is unpublished.
    Gina · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Workhard · 5 years ago
    Thanks a bunch, this really helped me out today!!!