Nested IF statements in Excel using Dates

Question

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

It will only hit IF(K3>I3,"COMPLETED","BARE") if I3>H3 if false anyway. I look at your data, and I3 is greater than H3. 12/15 is after 12/10. So I would expect it to show "IN PROCESS", no matter what the finish data is. Maybe you just need to be nesting your 3rd conditional here inside of the true case of your second conditional, not the false case. — AlanSE
– AlanSE, Commented Dec 20, 2017 at 22:02

barry houdini · Accepted Answer · 2017-12-20 23:36:58Z

1

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")

answered Dec 20, 2017 at 23:36

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jeprubio · Accepted Answer · 2017-12-20 22:06:08Z

0

Try this

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

answered Dec 20, 2017 at 22:06

jeprubio

18.1k5 gold badges51 silver badges62 bronze badges

2 Comments

James Hebert Over a year ago

It wont let me Upvote this, as Im a new member... but I modified what you have provided here slightly to get the following. and it works perfectly. =IF(H3 <> "", IF(K3 > I3,"COMPLETED", IF(I3>H3, "IN PROCESS","BARE")),"")

jeprubio Over a year ago

I think you can mark the answer as correct even having a low score. And also delete the question if you think it won't help others, do as you like. I'm happy to help :)

e_i_pi · Accepted Answer · 2017-12-20 22:11:25Z

0

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

If K3 exists and K3 > I3, the status is COMPLETE
If I3 exists and I3 > H3, the status is IN PROGRESS
If H3 exists, the status is RECEIVED
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")))

answered Dec 20, 2017 at 22:11

e_i_pi

4,8605 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

Nested IF statements in Excel using Dates

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related