Help with Formula

Could someone please help me with a formula?

Basically I want a formula to return a "Yes" if any data in the Area column = Location 1, but between a specified date range. If Location 1 is not in the area column, then return a "No". Thank you.


Best Answer

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓

    I came up with this formula and it worked for me when I tested it out. Go ahead and give it a shot and let me know if it doesn't work for you.


    =IF(AND(HAS(Area@row:Area@row, "Location 1"), NETDAYS([Start Date]@row, [End Date]@row) > 0) = true, "Yes", "NO")

Answers

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓

    I came up with this formula and it worked for me when I tested it out. Go ahead and give it a shot and let me know if it doesn't work for you.


    =IF(AND(HAS(Area@row:Area@row, "Location 1"), NETDAYS([Start Date]@row, [End Date]@row) > 0) = true, "Yes", "NO")

  • Jeff M.
    Jeff M. ✭✭✭

    If your sheet has only one date column, then you could do something like this:

    =IF(AND(HAS(Area@row:Area@row, "Location 1"), [Date]@row >= TODAY(-7)) = true, "Yes", "NO")

    This will check to see if the date is within the last 7 days.

    OR convert the date to a 1-52 weeknumber to determine if the date entered is greater or less than that week.

    =IF(AND(HAS(Area@row:Area@row, "Location 1"), WEEKNUMBER([Date]@row) < 40) = true, "Yes", "NO"

  • tsammons
    tsammons ✭✭✭

    Thanks Jeff M. That worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!