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

If you want to perform mathematical operations with date, you cannot use the TEXT function. Use INT(AI9834) instead.

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