Highlight rows with elapsed time in Duration column

Paolo Mauro

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

Genevieve P.

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