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

    bassam.khalil2009@gmail.com

    ☑️ 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

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

    bassam.khalil2009@gmail.com

    ☑️ 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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Paolo Mauro

    Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

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

    @Paolo Mauro

    For the second question, yes i can do that but it will take time but for now am sorry because am so busy.

    maybe we can talk in future, keep my Email so we can contact:

    Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Thanks Bassam, lets keep in touch, Paolo

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Paolo Mauro

    You are welcome and am happy to help you any time

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!