How to Easily Remove the Time Component from Dates in Excel

Working with dates in Excel can be tricky sometimes. By default, Excel stores dates and times as serial numbers. So even if a cell displays a date, there is also a time associated with it. Often you may want to extract just the date part without the time.

Removing the time component from dates in Excel allows you to work with and analyze just the dates, ignoring the time portion. This can be useful in many situations such as removing time from date fields imported from other sources, consolidating dates by ignoring time, and more.

In this comprehensive guide, I will cover several easy methods to remove time from dates in Excel using formulas, Find & Replace, Text to Columns, and by changing date formats.

Why Do Excel Dates Contain Time Components?

Before jumping into the methods it’s important to understand how Excel stores dates.

Excel uses a serial date system to represent dates. Each day is assigned a sequential serial number, with January 1, 1900 being serial number 1.

Times are stored as decimal fractions. For example, 6:00 AM is stored as 0.25 (one quarter of a day) and 12:00 PM is 0.5 (half a day).

So a full date plus time in Excel is stored as a single number that combines the serial date and decimal time

For example, January 1, 2022 at 5:30 PM is stored as 44902.72916667.

The integer part (44902) stores the date, while the decimal fraction (0.72916667) stores the time.

Hence, even if a cell is formatted to display only the date, the time information is still there behind the scenes!

Now let’s look at a couple of simple ways to remove the time portion from dates in Excel.

Method 1: Change the Date Format

The fastest way to hide time from dates in Excel is by changing the cell formatting:

1. Select the cells containing the date timestamps.

2. Press Ctrl + 1 to open the Format Cells dialog.

3. On the Number tab, click Date from the Category list.

4. Select a date-only format like 3/14/2001 or March 14, 2001 from the Type list.

5. Click OK.

This will hide the time while keeping the time data stored behind the scenes. The original date + time value will still be used in calculations.

Pros:

• Fast and easy.
• Does not alter the original data.

Cons:

• Time is hidden but still exists behind the scenes.

Method 2: Use the INT Function

To completely remove time from dates, you can use Excel’s INT function. INT returns the integer part of a number, truncating the decimal portion.

For example:

=INT(44902.72916667)

Returns 44902, removing the time portion.

Steps to remove time using INT:

1. In a new column, enter the INT formula referencing the date cell.

E.g. if date is in A2, use =INT(A2)

1. Format the new column as Date to only show the date.

Pros:

• Completely removes time from dates.
• Simple formula.

Cons:

• Changes the original date values.
• Needs a new column.

Method 3: Use the DATEVALUE Function

Another way to strip time using Excel formulas is with DATEVALUE.

DATEVALUE returns only the date portion from a date/time input.

Since DATEVALUE requires a text string input, use TEXT to convert to text first:

=DATEVALUE(TEXT(A2,"mm/dd/yyyy")) 

Pros:

• Removes time and keeps only date.
• Does not alter original data.

Cons:

• Requires an extra column.
• Nested formula can get complicated.

Method 4: Use Find and Replace

If your dates are separated from time by spaces or other delimiters, Find & Replace can quickly remove the time portions.

For example:

1/15/2020 12:30 PM

Steps:

1. Press Ctrl + H to open Find & Replace.

2. In Find what, enter a space followed by an asterisk ( *)

3. Leave Replace with blank.

4. Click Replace All.

This will remove everything after the space, deleting the time text.

Pros:

• Very fast for large datasets.
• Keeps original date formatting.

Cons:

• Only works if date/time are separated by spaces/delimiters.
• Permanently deletes time data.

Method 5: Use Text to Columns

The Text to Columns wizard can also be used to remove time from dates in Excel.

This method is great when you want to split the date and time into separate columns.

Steps:

1. Select the date cells. Go to Data > Text to Columns.

2. Select Delimited > Next. Check Space as the delimiter.

3. On the final screen, select the time column and choose ‘Do not import column’.

4. This will split the date and time into two columns, then delete the time column.

Pros:

• Splits date and time into separate columns.
• Does not alter original data.

Cons:

• Requires many steps.
• Not ideal if you just want dates without time.

Things to Remember!

Here are some important pointers when removing time from dates in Excel:

• Formatting hides time but does not remove it. Use formulas or Find&Replace to completely remove time.

• Methods like Find&Replace and Text to Columns permanently delete time data. Keep the original column intact.

• Try to remove time in a new column if you want to retain original date+time values.

• Make sure date formats are changed to view dates correctly after removing time.

• Removing time sets the time portion to 12:00 AM by default in Excel.

Quickly Remove Time from Dates for Data Analysis

Dates in Excel contain hidden time values. There are many simple ways to remove the time component from dates for clean data analysis and reporting.

Frequency of Entities:
trumpexcel.com: 2
wikihow.com: 1