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
-
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
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"
-
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
-
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
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"
-
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
-
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.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"
-
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
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
-
You are welcome and am happy to help you any time
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!