Skip to main content
Author: Xiaoyang Last Modified: 2024-01-24

When working with date-time data in Excel, you may encounter datetime values like "1/24/2024 14:30:00," and you might need to extract only the date "1/24/2024" without the time part. This article will introduce you to four simple methods to remove the time from the timestamp. These methods are simple and easy to understand, suitable for various situations, and whether you are a beginner or an experienced Excel user, you can quickly learn and apply them.

Hide the time from date by changing format

Remove time from date by using a smart feature

Remove time from date by using formulas

Remove time from date by using Find and Replace function


Hide the time from date by changing format

  1. Select the cells you want to remove time, and right click, in the context menu, choose Format Cells to open the Format Cells dialog box. See screenshot:
    Tip: You can also use the shortcut Ctrl +1 to open the Format Cells dialog box.

  2. In the Format Cells dialog, under the Number tab:
    1). Select Date from the Category list;
    2). Select one type of date as you need from the Type list box;
    3). Finally, click OK button.
  3. Now, the selected cells are formatted to display only the date, with the time removed. see screenshot:
Note: This method only conceals the time portion without actually removing it, the date and time can still be seen in the formula bar. Additionally, if these cells are utilized in any calculations, the values will include both the date and the time.

Remove time from date by using a smart feature

If you want to remove the time from the time stamp completely, Kutools for Excel offers a smart feature - Remove time from date, with this feature, you can directly remove time from the datetime cell with only several clicks.

Note: If you want to use this Remove time from date feature, please download and install Kutools for Excel first.

After installing Kutools for Excel, please do as this:

  1. Click to select a cell where you want to place the result, then click Kutools > Formula Helper > Formula Helper.
  2. In the Formulas Helper dialog, click the Remove time from date in the Choose a formula section.
  3. Then go to the Argument input section, select a cell that you want to remove time from.
  4. At last, click OK button.

Result:

Now, the time has been removed from the datetime, then drag fill handle over the cells you want to apply this formula as you need, see the demo below:

Tip: To apply this feature, please download and install Kutools for Excel first.

Remove time from date by using formulas

In Excel, there are some simple formulas that can be used to eliminate the time from a timestamp, allowing you to extract only the date portion.

Using INT function to extract only date

  1. Please enter or copy the following formula into a cell where you want to put the result. Then, drag the fill handle down to fill this formula to the rest cells, you will get the date with time part (00:00:00) in the result, see screenshot:
    =INT(A2)
  2. To not show the time part, still keep the results selecting, and then, click Home > General > Short Date. Now, the results are displayed as date only. See screenshot:

Using DATE function to extract only date

To extract only the date part, you can also use the DATE function.

Generic syntax:

=DATE(YEAR(datetime),MONTH(datetime),DAY(datetime))
datetime: The cell contains the datetime that you want to extract date only from.

Please enter or copy the following formula into a cell where you want to put the result. Then, drag the fill handle down to fill this formula to the rest cells, all the dates have been extracted from the datetime cells without time. See screenshot:

=DATE(YEAR(A2),MONTH(A2),DAY(A2))


Remove time from date by using Find and Replace function

Follow these steps to remove time from dates using Excel's Find and Replace function:

  1. Select the date range that you want to remove the time.
  2. Press Ctrl + H or navigate to Home > Find & Select > Replace to open the Find and Replace dialog box.
  3. In the Find and Replace dialog box:
    1). Enter a spacebar and an asterisk * into the Find what box;
    2). Leave blank in the Replace with box;
    3). Finally, click Replace All button.
  4. Now, all of the time have been removed from the date range, the date may still display with a time portion of 00:00:00. See screenshot:
  5. Then you can format the range to show only the date by clicking Home > General > Short Date. See screenshot:
Tip: Using Find and Replace is great because you can change your data right in the same cells where it's located, without needing extra columns. Just remember to make a backup copy in case you ever want the original data again later on. This way, you keep things tidy and safe!

Related Articles:

  • Remove date from date time in Excel
  • If you have a list of date and time stamps in your worksheet, and now you want to remove the date from the date time and only leave the time. Do you have any quick methods to deal with this job?
  • Combine text and date into same cell in Excel
  • Supposing you have two columns which one contains text strings and another is filled with date, now, you want to combine these two columns to get the following screenshot result. Most of us may concatenate the two columns directly, but the date will be displayed as integer number. This article, I will introduce some effective methods to correctly combine text and date into one cell in Excel.
  • Extract date from text strings in Excel
  • In Excel worksheet, how could you extract the date from text strings as following screenshot shown? This article, I will talk about a useful formula to solve it.
  • Extract month and year only from date in Excel
  • If you have a list of date format, now, you want to extract only the month and year from date as left screenshot shown, how could you extract month and year from the date quickly and easily in Excel?
Comments (32)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Just copy the columns and paste them in a txt file, the txt file will not show the hours and minutes. Copy the dates from the txt and paste it back to the xls file. pretty easy
This comment was minimized by the moderator on the site
Copy to a cell and format as general (MS Date Value), then ROUNDDOWN,0 Simple.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
This was so helpful and worked for my needs exactly! Thank you!
This comment was minimized by the moderator on the site
Thanks a lot! For me it worked. Is funny you can found better solutions in the comments section than in the actual post ;)
This comment was minimized by the moderator on the site
useful, thanks
This comment was minimized by the moderator on the site
thank you !!!
This comment was minimized by the moderator on the site
cara show de bola
muitissimo obrigado
This comment was minimized by the moderator on the site
Thanks you so much , for that replace option work,,thank you
This comment was minimized by the moderator on the site
Thank you! This helped me a great deal to make dates so that I can then create charts showing accurate trends in performance.
This comment was minimized by the moderator on the site
just use formula =int(date)
This comment was minimized by the moderator on the site
dropping the time with a global replace - GENIUS, dude (...or dudette).
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations