Articles → EXCEL → Remove the time from a date and time cell in excel

Remove the time from a date and time cell in excel

In this article, we will see – how to remove time from a date and time cell in excel. For this we will follow 2 approaches. But before that, let us check the sample excel data on which we are going to work.

Excel file

For this article, we will be using the excel file with following data.

Click to Enlarge

Let us work on each approach.

Approach 1

In this approach

1. Select the range of cells.

Picture showing selecting the range of cells in excel

Click to Enlarge

   2. Press CTRL + H to open find and replace window.

   3. In the ‘Find What’ textbox, enter ‘ *’.

   4. In the ‘Replace with’ textbox, keep it blank.

Picture showing the Find and Replace window for removing the time

Click to Enlarge

5. Click on ‘Replace All’ button.

All the time element will be removed

Picture showing removing the time element from the date string

Click to Enlarge

Approach 2

In this approach

1. Select the range of cells.

Click to Enlarge

2. Go to the ‘Number’ section of the ‘Home’ ribbon and select ‘General’ from the drop down.

Picture showing selecting the General option from the dropdown

Click to Enlarge

3. Data is converted into numbers.

Picture showing the dates are converted into numbers

Click to Enlarge

Here number before the decimal is date and number after decimal is time.

   1. Press CTRL + H to open find and replace window.

   2. In the ‘Find What’ textbox, enter ‘.*’.

   3. In the ‘Replace with’ textbox, keep it blank.

Picture showing Find and Replace window for removing decimals

Click to Enlarge

4. Click on ‘Replace All’ button.

5. Decimals are removed from the number.

Picture showing removing the decimal and numbers after decimal

Click to Enlarge

6. Now again go to the ‘Number’ section of the ‘Home’ ribbon and select ‘Short Date’ from the drop down.

Picture showing selecting the Short Date option from the dropdown

Click to Enlarge

Time is removed now from date and time cell.

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Friday, December 21, 2018

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250