Using TODAY() as a date range

davidg

Hi All, I was hoping to be able to use the TODAY() function as part of a date range check using the IF and AND functions then using column name >= TODAY, column name <= TODAY() in a nested IF statment.

=IF(Complete1 = 1, "Up", IF(Start1 = TODAY(), "Angle Up", IF(AND(Start1 >= TODAY(), Finish1 <= TODAY()), "Sideways", IF(Finish1 < TODAY(), "Down"))))

Everything works it's just when I add the IF(AND(.... in the formula it returns nothing at all regardless of the date it is checking.

Paul Newcome

Try swapping the symbols in the AND statement.

 

Start1 <=

Finish1 >=

davidg

Thanks for the reply but I've tried removing the equals sign and just using greater than and less than but no joy 

davidg

and also tried your suggestion and it still returning a blank result

J. Craig Williams

You are missing important parts of your logic.

Is this what you are looking for?

1. Complete = Up

all below are Not Complete

2. Finish in Past = Down

3. Start is Today = Angle Up 

4. Start in Future = ??

5. Start in Past = Sideways

Assumption: Finish always >= Start 

If so, try this:

=IF(Complete@row = 1, "Up", IF(Finish@row < TODAY(), "Down", IF(Start@row > TODAY(), "TBD", IF(Start@row = TODAY(), "Angle Up", IF(Start@row < TODAY(), "Sideways", "Not Accounted For")))))

and change "TBD" to your choice for #4

Craig

davidg

I've tried getting the problem part to work in isolation and still can't

 IF(AND(Start1 >= TODAY(), Finish1 <= TODAY()), "Sideways") s

any suggesstions would be welcomed. Oh and I replaced the TODAY function with the DATE function and it works. 

Can anyone offer some insight into why the TODAY function can't be used int the above scenario? Just to give you some context as to why I need to use the TODAY function it is being applied across rolling dates 

J. Craig Williams

This formula:

IF(AND(Start1 >= TODAY(), Finish1 <= TODAY()), "Sideways")

is only "Sideways" when both Start and Finish are TODAY(), UNLESS your dates are backwards.

I don't know how you replaced TODAY() with DATE(). That's seems odd.

Did you read my previous post?

 

Craig

 

davidg

Hi Craig and thanks for the info so far. I replaced the TODAY function with a DATE nby setting a date range eg 

=IF(AND(Start1 >= DATE(2018, 7, 18), Finish1 <= DATE(2018, 7, 24)), "Sideways")

 

davidg

this is a better screenshoot of what I'm trying to do

davidg

here it is using today function

davidg

I think I understand now, my logic is flawed as you mentioned in your original post when trying to figure out what I was trying to achieve. It can only be the actual day for it to work. 

Thanks for your help Craig

davidg

keep up the good work Craig. I've seen you in other posts offering advice and resolving issues. 

again thanks