Date range formula

naomi79266
edited 12/09/19 in Smartsheet Basics

I am trying to write a date range formula with an IF AND statement (I think). 

I need to check a box if [Previous date] is in a specific date range. 

Comments

  • Brian W
    Brian W ✭✭

    It should look something like this:

    =IF(AND([Previous date]@row <= DATE(2019, 1, 31), [Previous date]@row >= DATE(2019, 1, 1)), 1, 0)

    This one will flag all dates in January 2019. You'll need to adjust the dates manually. The 1 and 0 at the end of the formula turn the checkbox on and off.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    When you only have two results (checked or unchecked), you really only need to specify what changes the default of unchecked to checked. You could leave the 0 off of the end of the formula and it will still work the same way. (Just FYI).

    .

    A few additional things to keep in mind that may or may not make things a little easier for you...

    .

     To help with having to manually adjust dates within a formula (if you're anything like me you stand a good chance of a typo or fat fingering something haha), you could reference 3 (or 6) different cells depending on the type of breakdown you are looking for. Something along the lines of (column headers are bold)...

    .

    Helper          Start          End

    Year

    Month

    Day

    .

    You could then use (assuming the Year row is row 1)

     

    =DATE(Start$1, Start$2, Start$3)

    .

    This gives you the ability to edit your dates without having to edit your formula. If you are looking for weekly, monthly or yearly counts you could use 

    =IF(WEEKNUMBER([Previous Date]@row) = *********, 1)

    In the section of ********* you have a few options. You could enter a specific number, WEEKNUMBER(a cell reference that would contain a number), or WEEKNUMBER(a date using the DATE function which could also be manual entry or cell references).

    .

    =IF(MONTH([Previous Date]@row) = ************, 1)

    .

    =IF(YEAR([Previous Date]@row) = ***********, 1)

    .

    =IF(AND(YEAR([Previous Date]@row) = **********, MONTH([Previous Date]@row) = **********), 1)