CODEWITHSUNDEEP
excelnested
James Hebert
James Hebert

Reputation: 33

Nested IF statements in Excel using Dates

Ive searched far and wide on Stackoverflow, and I cannot seen to locate my question. I clearly am having an issue with the nested IF statement, but i cannot find any reference question to duplicate my situation.

Here is what I am "trying" to do.

"Received" Cell H3 = 12/10/2017
"Start Date" Cell I3 = 12/15/2017
"Finish Date" Cell K3 = 12/20/2017

"Status" Cell J3 is where I want the following options to show up.

If Date present in H3 (Received), show "Bare". If Date shown in I3 (Start Date) Greater/Later than H3 (Received Date), show "In Process". If Date shown in K3 (Finish Date) is Greater/Later than I3 (Start Date, then J3 should reflect "Completed". If nothing shown in H3 (Received), show Nothing in J3.

This is the string that I am using, and it works in all aspects EXCEPT it doesn't show "Completed" when I add the "Finish Date" in. Since it works kinda, but not all the way is where I admit defeat. I need an error to chase if I am doing to diagnose with any luck lol 

=IF(H3<>"",IF(I3>H3,"IN PROCESS",IF(K3>I3,"COMPLETED","BARE")))

ANY Help would be appreciated, im sure its probably something rather simple in regards to my eye not seeing a comma... but if im way off im eager to learn!
Thanks in advance.
JH

Upvotes: 2

Views: 3099

Answers (3)

barry houdini
barry houdini

Reputation: 46401

Wouldn't the Finished date always be >= Start Date and Start Date >= Received Date?

If so then perhaps just count how many dates there are. If there's one date (Received) status is BARE, if there are two then IN PROCESS, if there are 3 it's COMPLETED (and no dates just returns a blank)

=CHOOSE(COUNT(H3,I3,K3)+1,"","BARE","IN PROCESS","COMPLETED")

Upvotes: 1

e_i_pi
e_i_pi

Reputation: 4820

The logic as you've described it is kind of wonky. I think it's better to structure your statements like this:

  1. If K3 exists and K3 > I3, the status is COMPLETE
  2. If I3 exists and I3 > H3, the status is IN PROGRESS
  3. If H3 exists, the status is RECEIVED
  4. Else the status is BARE

I believe that's what you're after. In that case, this algorithm satisfies that logic:

=IF(AND(K3<>"",K3>I3),"COMPLETED",IF(AND(I3<>"",I3>H3),"IN PROCESS",IF(H3<>"","RECEIVED","BARE")))

Upvotes: 0

jeprubio
jeprubio

Reputation: 18032

Try this

=IF (H3 <> "", IF (K3 > I3, "COMPLETED", IF (I3>H3, "IN PROCESS", "BARE")))

Upvotes: 0

Related Questions