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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!