CODEWITHSUNDEEP
excel
416NismoZ
416NismoZ

Reputation: 1

Excel formula has no errors but it not working as expected

I'm trying to create a spreadsheet that returns if an item was completed on time or not. "A" column is either "Open" or "Closed". If the "A" column is "Open" then I want to return a blank cell. If the "A" column is "Closed" then it needs to calculate dates in column "B" and "X" to see if it was on time or not. The first part of the formula is returning blanks as it should. The two "IF(AND" statements that follow seem to be causing all cells in column "A" that are "Closed" to return a "No". Been digging at this for two days trying different ways of writing a formula with no success. The "X" column is calculated by adding two columns together...not sure if that makes a difference.

=IF(A2="Open"," ",IF(AND(A2="Closed",X2>=B2),"Yes",IF(AND(A2="Closed",X2<B2),"No")))

I expect if A2="Open" then it would return a blank cell. This is working as expected.

I expect if A2="Closed" then X2>=B2 would return a "Yes" and if X2<B2 it would return a "No". There are no errors identified by Microsoft Excel.

Upvotes: 0

Views: 89

Answers (2)

xidgel
xidgel

Reputation: 3145

It may be that Excel is interpreting your dates as strings instead as dates. You can investigate by:

  1. Open your formula in the editor and select B2.
  2. While B2 is selected, hit the F9 key to show its value.
  3. If it's a date value F9 will convert it to a number like 41609. If it's a string you'll get "May 16, 2019".
  4. Hit CTRL-Z to revert back to the original formula.
  5. Repeat for X2.

Hope that helps

Upvotes: 0

Seb
Seb

Reputation: 1230

If A2 can only be "Open" or "Close", syntax of your If should be :

=IF(A2="Open"," ",IF(X2>=B2,"Yes","No"))

Upvotes: 2

Related Questions