Duration column formula: two situations
Hello,
I have three date columns involved in the duration:
Date Submitted
Date Completed
For Future Requests
My current duration formula only uses the Date Submitted and Date Completed columns:
=IF(Status@row <> "Complete", NETWORKDAYS([Date Submitted]@row, TODAY()), NETWORKDAYS([Date Submitted]@row, [Date Completed]@row))
I want to create a formula that incorporates two situations: 1) number of weekdays between Date Submitted and Date Completed when "Future Request" is unchecked or 2) number of weekdays between For Future Requests and Date Completed when "Future Request?" is checked.
Any help is much appreciated!
Best Answers
-
Hi @Brooks
Try this:
=IF([Future Request]@row = 0, IF(Status@row <> "Complete", NETWORKDAYS([Date Submitted]@row, TODAY()), NETWORKDAYS([Date Submitted]@row, [Date Completed]@row)), IF(Status@row <> "Complete", NETWORKDAYS([For Future Requests]@row, TODAY()), NETWORKDAYS([For Future Requests]@row, [Date Completed]@row))
It repeats your same formula... once for if the checkbox is 0, or un-checked, and then again if the checkbox is 1, but swapping out [Date Submitted]@row for [For Future Requests]@row .
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
No problem! You can just add another statement right at the front:
=IF(Progress@row <> "Future Request", IF([Future Request]@row = 0, IF(Status@row <> "Complete", NETWORKDAYS([Date Submitted]@row, TODAY()), NETWORKDAYS([Date Submitted]@row, [Date Completed]@row)), IF(Status@row <> "Complete", NETWORKDAYS([For Future Requests]@row, TODAY()), NETWORKDAYS([For Future Requests]@row, [Date Completed]@row)), "")
This will be Blank if it's a Future Request. Does this do what you would like it to?
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Brooks
Try this:
=IF([Future Request]@row = 0, IF(Status@row <> "Complete", NETWORKDAYS([Date Submitted]@row, TODAY()), NETWORKDAYS([Date Submitted]@row, [Date Completed]@row)), IF(Status@row <> "Complete", NETWORKDAYS([For Future Requests]@row, TODAY()), NETWORKDAYS([For Future Requests]@row, [Date Completed]@row))
It repeats your same formula... once for if the checkbox is 0, or un-checked, and then again if the checkbox is 1, but swapping out [Date Submitted]@row for [For Future Requests]@row .
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thanks, @Genevieve P.!
-
Hi @Genevieve P.,
Is there a way to edit this formula so that the above formula only works if the Progress column does not display "Future Request"?
Thanks for your assistance!
-
No problem! You can just add another statement right at the front:
=IF(Progress@row <> "Future Request", IF([Future Request]@row = 0, IF(Status@row <> "Complete", NETWORKDAYS([Date Submitted]@row, TODAY()), NETWORKDAYS([Date Submitted]@row, [Date Completed]@row)), IF(Status@row <> "Complete", NETWORKDAYS([For Future Requests]@row, TODAY()), NETWORKDAYS([For Future Requests]@row, [Date Completed]@row)), "")
This will be Blank if it's a Future Request. Does this do what you would like it to?
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Perfect, @Genevieve P. Thanks so much for your help! I am still getting to know IF clauses.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 359 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!