• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested if or conditions

srinidhi

srinidhi

Active Member
Hi All, need you help in solving the multiple if or conditions

A1: Start Date (SD)
B1: End Date (ED)
C1: to have the result based on the following conditions.

There are 3 conditions:
If SD or ED is less than today "History"
If SD or ED is greater than today "Future"
If SD or ED is equal to today "Present"
 
Hi Srinidhi ,

Will both StartDate and EndDate always have dates in them , or do we have to consider that one or both may be blank ? If yes , then what should be the output when either or both are blank ?

Narayan
 
Hi Srinidhi ,

One more clarification !

Suppose one of them is in the past and the other is in the future , what should be the output ?

Suppose one of them is today's date , and the other is in the past or the future , what should be the output ?

We need to consider all the possibilities :

StartDate = Past , EndDate = Today
StartDate = Today , EndDate = Today
StartDate = Future , EndDate = Today

StartDate = Past , EndDate = Past
StartDate = Today , EndDate = Past
StartDate = Future , EndDate = Past

StartDate = Past , EndDate = Future
StartDate = Today , EndDate = Future
StartDate = Future , EndDate = Future

What should be the output in each case , or can some of the cases be ruled out as impossible in practice ?

Narayan
 
Hi Srinidhi,

If SD=12/5/2014 [History] and ED=12/15/2014[Future] then what will be the expected result?

If SD=12/5/2014 [History] and ED=12/6/2014[Present] then what will be the expected result?

If SD=12/6/2014 [Present] and ED=12/15/2014[Future] then what will be the expected result?
 
@ Narayan

StartDate = Past , EndDate = Today Present
StartDate = Today , EndDate = Today Present
StartDate = Future , EndDate = Today It is not possible,as the start date cant be in future & end date today

StartDate = Past , EndDate = Past History
StartDate = Today , EndDate = Past It is not possible,as the start date cant be today & end date be in past
StartDate = Future , EndDate = Past It is not possible,as the start date cant be in future & end date in past

StartDate = Past , EndDate = Future Future
StartDate = Today , EndDate = Future Future
StartDate = Future , EndDate = Future Future
 
@ Shrivallabha

If SD=12/5/2014 [History] and ED=12/15/2014[Future] then what will be the expected result? Future

If SD=12/5/2014 [History] and ED=12/6/2014[Present] then what will be the expected result? Present

If SD=12/6/2014 [Present] and ED=12/15/2014[Future] then what will be the expected result? Future
 
Hi Srinidhi ,

Going by your reply to my post , I think the StartDate is irrelevant ; you seem to be looking at only the EndDate. Is this correct ?

Narayan
 
Hi Srinidhi ,

In which case Shrivallabha's formula can be simplified a little more to :

=IF(EndDate = TODAY() , "Present" , IF(EndDate < TODAY() , "Past" , "Future"))

Narayan
 
srinidhi said:
Thanks, Shrivallabha, this is what I was looking for.
Click to expand...
Good to know that.

Narayan's questions were great example of spending the time on defining the question correctly which most important part of solving a situation. And just for another option what else but LOOKUP.

=LOOKUP(B1,TODAY()+{-1,0,1},{"History","Present","Future"})
 
You must log in or register to reply here.
Back
Top