Remove time from date field in Excel formula

Question

Following up from my previous question, I need to add on to the formula a way to remove the time from a datetime column.

My current working formula is as follows:

=IF(OR(AD9834="In progress",BB9834=""),ROUND($BW$1-AI9834,0),IF(AD9834="Reopened",ROUND(BW1-AP9834,0),ROUND(BW1-AI9834,0)))

In the AI column, the datetime looks like this:

5/24/2021 1:20:52 PM

I basically need to remove the time from the formula.

I tried to do the following:

=IF(OR(AD9834="In progress",BB9834=""),ROUND($BW$1-AI9834,1),IF(AD9834="Reopened",ROUND(BW1-AP9834,0),ROUND(BW1-AI9834,1)))

But that does not remove the time.

How can I make this work?

Answer 1

Use the Find And Replace function to delete the time from a date.

To remove time from a date, first, highlight the cells.

Then, on the Ribbon, go to Home. Select Find & Select from the Editing group.

Select Replace from the pop-up menu.

Finally, in the Find and Replace box, type the space bar and * in the Find what box, and leave the Replace with box blank, then click Replace All.

Following that, the date is omitted.

To Remoce time ,follow these steps

Step 1: Creating a timestamp by formatting data.
Right-click on the cell and select Format Cells...
Step 2: Select Custom in Category and dd-mm-yyyy hh:mm in Type in the Formal Cells box, and then click Ok.
Then, in the cell, we'll enter our date and time.
Format Cells can be used to remove the time from a date.
Step 1: To remove the time from the date, highlight the cells.
Step 2: Next, right-click it and select Format Cells...
Step 3: Select Date in Category and *14-03-2012 in Type in the Format Cells box, then click Ok.

.