Highlight rows with elapsed time in Duration column
I would like to setup a formula /if statement, that looks in the standard "duration" column which would typically be days or weeks, and puts in another column 24 if duration type is "elapsed" (ie e2wk) or 8 if its just a standard duration (ie 2wk)
Is there a way to use the duration elapsed/non-elapsed column to do this?
thanks
Answers
-
Hi @Paolo Mauro
Currently the "e" in a Duration column used for Elapsed time cannot be recognized by a formula. For example, if you use a simple =Duration@row into a text column, you'll notice that just the number will be returned (without the "e" or "d" or "wk", etc).
This means we cannot use the CONTAINS function to search for "e", since although the display text shows an e in the cell, the column is being used in Project Settings so the formula will skip over that and only see the number associated.
What we can do is check the dates of the task and check the NETWORKDAYS between these two dates. If the working days between those dates are less than the duration number, then we know that the duration accounts for weekend dates:
=IF(NETWORKDAYS(Start@row, Finish@row) < Duration@row, "Elapsed", "Not Elapsed")
or
=IF(NETWORKDAYS(Start@row, Finish@row) < Duration@row, 24, 8)
Will this work for your purposes?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!