# Using TODAY() as a date range

edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭

Try swapping the symbols in the AND statement.

Start1 <=

Finish1 >=

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

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

• ✭✭✭✭✭✭

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

• edited 07/17/18

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

• ✭✭✭✭✭✭

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

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

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

• here it is using today function

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

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

again thanks