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?

Mar 25, 2022 in Database 739 views

## 1 answer to this question.

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.

.
• 23,260 points

## Excel formula to remove space between words in a cell

There are three fast techniques to get ...READ MORE

## how to change format of date from mm/dd/yyyy to dd-mmm-yyyy in MS Excel

First, pick the cells that contain dates, ...READ MORE

## Split timestamp column into separate date and time columns in excel

Kutools for Excel allows you to split ...READ MORE

## Date difference: different results in Excel vs. Python

To calculate the date difference, we can ...READ MORE

## How to sort dates from Oldest to Newest in Excel?

Drag down the column to select the ...READ MORE

## Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

## EXCEL: How to automatically add serial number in Excel Table using formula that is immune to filtering / sorting?

That's a great question, and it's a ...READ MORE

## Excel formula for reverse percentage calculation

I have a calculation which I am ...READ MORE