Using TODAY() as a date range

davidg
davidg
edited 12/09/19 in Smartsheet Basics

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.

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try swapping the symbols in the AND statement.

     

    Start1 <=

    Finish1 >=

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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

  • J. Craig Williams
    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
    davidg
    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 

  • J. Craig Williams
    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

     

    Sideways.png

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

     

    date.JPG

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

    date1.JPG

  • here it is using today function

    date2.JPG

  • 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

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

    again thanks