Flag if date range across weekend

Dear Smartsheet, first, great product and I have been able to learn alot on this forum....however

I am a little stuck with a function/formula. I have a list of tests which is dynamic and changes frequently due to predecessors.

I would like to be able to Flag column "Weekend Run" if the test date range occurs over a weekend AND if "Test Type = Endurance".

I have tried some combination of index/collect and countifs/range statements but no luck, I would really appreciate some help

Many thanks

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/04/21 Answer ✓

    Hi @Paolo Mauro

    Hope you are fine, please try the following formula and convert it to column formula:( weekend is Saturday & Sunday ) and 5

    =IF(AND(Duration@row >= 6, [Test Type]@row = "Endurance"), 1, IF(AND(Duration@row < 6, [Test Type]@row = "Endurance", OR(WEEKDAY(Finish@row) = 7, WEEKDAY(Finish@row) = 1)), 1))

    the following screenshot show the result

    PMP Certified

    [email protected]


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Paolo Mauro
    Answer ✓

    Hi Bassam, 2 times WOW 1) works perfect 2) super fast response, fantastic!!

    2nd step, I would like to populate a 2nd sheet which would then show all "weekend" tests from different sheets with only the 2 days (Saturday/Sunday) where weekend run flagged. If a test extends over 3 weekends, then I would want to have 3 different rows, each row with the 2 days Sat/Sun...then I can assign these individual weekends to users. Does this 2nd step also have a slick solution?

    Many thanks again, Paolo


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!