Flag if date range across weekend

Paolo Mauro

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

Bassam Khalil

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

Paolo Mauro

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

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

Paolo Mauro

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

@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.

Bassam Khalil

@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:

[email protected]

Paolo Mauro

Thanks Bassam, lets keep in touch, Paolo

Bassam Khalil

@Paolo Mauro

You are welcome and am happy to help you any time