IF AND statement using TODAY function also

JaredM
JaredM
edited 12/09/19 in Smartsheet Basics

Hello Community,

I am trying to get "Yes" to appear within a column in the event that a checkbox type column is checked, and the date found in a date column is 7 days old, or older. Here is what I have at the moment:

=IF(AND((Complete2 = 1, "Yes", ""), [Due Date]2 > TODAY() - 7), "Yes", "")

 

Any help with this would be appreciated, thanks!

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Give this a try...

     

    =IF(AND([Complete2] = 1, [Due Date]2 > TODAY() - 7), "Yes", "")

    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

  • Hey Paul,

     

    Thanks for the reply. That didn't seem to work for some reason. 

    Here, I've attached a screenshot of the sheet I'm working on--it's fairly simple. I'm just trying to achieve the Archive column populating as a "yes" when the task is complete *and past the due date for the task.

    Thanks!

    TaskManager_Sheet.PNG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Ok. The screenshot helped. Thry this one instead...

     

    =IF(AND(Complete@row = 1, TODAY() - [Due Date]@row >= 7), "Yes", "")

    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

  • Hello community, I cannot figure out why this formula is #UNPARSEABLE in Smartsheet when it works perfectly in Excel. The intent is to show the amount of days left before/after "Due Date" [Column5].

    EXCELL

    =IF(E2="","",IF(E2<TODAY(),-DATEDIF(E2,TODAY(),"d"),DATEDIF(TODAY(),E2,"d")))

    SMARTSHEET

    =IF([Column5]2="","",IF([Column5]2<TODAY(),-DATEDIF([Column5]2,TODAY(),"d"),DATEDIF(TODAY(),[Column5]2,"d")))