• 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.

There is a bug in my Soup (Formula) !!

arishy

Member
Given Reference Date rDate
Given Now Date nDate TODAY()
Given Number of days nDays

IF(AND(rDate>=nDate , rDate<=nDate + nDays , rDate<>"")),"GREEN","RED")

I get GREEN Only when rDate is between Now and (Now + nDays)
IF rDate is Blank I get RED.

Can I eat the soup ????? :oops:
 
Hi arishy

The Second condition is strong enough to capture the first condition. So where there is a failure in Part 2 it will cover off part 1 as well. So why not scrap part1 and just include Part 2 and Part 3.

=IF(AND(rdate +nDays>=nDate,nDays<>""),"Green","Red")

If my thinking is not correct on this get rid of that second bracket before the , “Green” and it should work a bit better.

Take care

Smallman
 
Hi ,

If I state your conditions in plain English , what you want is that the cell should turn GREEN under the following conditions :

1. If rDate is blank or
2. If rDate is between nDate and ( nDate + nDays )

The only way this can be tested for is by an OR condition , as follows :

=OR(rDate="",AND(rDate>=nDate,rDate<=(nDate + nDays)))

For the RED condition , you can just put a NOT outside the above formula , as in :

=NOT(OR(rDate="",AND(rDate>=nDate,rDate<=(nDate + nDays))))

or you can simplify this to :

=AND(rDate<>"",OR(rDate<nDate,rDate>(nDate + nDays)))

Narayan
 
Hi, arishy!

Give a look at the uploaded file. Now date is in A2 cell, no. of days is in B2 cell (7), reference dates are in range C2:C92 (last empty), and results are in range D2: D92 and they seem to be right according to your formula, so there's no bug in it, unless it's an elephant that I don't happen to see.

Regards!
 

Attachments

  • There is a bug in my Soup (Formula) !! (for arishy at chandoo.org).xlsx
    11.6 KB · Views: 4
I love you Guys and Gals...Having you around make it really fun to work with this highly created tool.:)
 
Back
Top