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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!