Remove time from date field in Excel formula

0 votes

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 by Edureka
• 13,690 points
898 views

1 answer to this question.

0 votes
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.

.
answered Mar 30, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

Excel formula to remove space between words in a cell

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

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
762 views
+1 vote
1 answer

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

answered Apr 11, 2022 in Database by gaurav
• 23,260 points
158,657 views
0 votes
1 answer

Split timestamp column into separate date and time columns in excel

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

answered Apr 11, 2022 in Database by gaurav
• 23,260 points
2,286 views
0 votes
1 answer

Date difference: different results in Excel vs. Python

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

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,112 views
0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

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

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
5,192 views
0 votes
1 answer

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

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

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
2,268 views
0 votes
1 answer
0 votes
0 answers

Excel formula for reverse percentage calculation

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

Apr 7, 2022 in Database by Edureka
• 13,690 points
987 views
0 votes
1 answer

Remove special characters from the specified string in excel

To erase a specific character from a ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
1,595 views
0 votes
1 answer

Prevent cell numbers from incrementing in a formula in Excel

In Excel, you can use a feature ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
10,282 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP