Formula to show whichever is less

carlycrisp

Hello,

I'm working on a formula to show the remaining duration of a task. I'd like to calculate workdays using the planned end date minus either today or the planned start date, whichever is closer to the planned end date. For example, if the planned start date is 10/29/21 and the planned end date is 11/05/21, then the formula would calculate the remaining workdays between today and 11/05/21. However, if the planned start date is 11/05/21 and the planned end date is 11/12/21, then the formula would calculate the number of workdays between 11/05 and 11/12. I'm stumped on how to write this formula. Any ideas?

Thanks!

Paul Newcome

You can use an IF statement for that.

If today is greater than the planned end, output zero, otherwise run the NETWORKDAYS calculation.

=IF(TODAY() >= [Planned End Date]@row, 0, NETWORKDAYS(MAX(TODAY(), [Planned Start Date]@row), [Planned End Date]@row))

Paul Newcome

So you essentially want to use the date that is greater (today or planned start)?

=NETWORKDAYS(MAX(TODAY(), [Planned Start Date]@row), [Planned End Date]@row)

carlycrisp

@Paul Newcome Thank you! That worked perfectly. I have a follow up question. Do you know what I can add to the formula so that tasks in which the planned end has already passed show as 0 instead of a negative number?

Paul Newcome

You can use an IF statement for that.

If today is greater than the planned end, output zero, otherwise run the NETWORKDAYS calculation.

=IF(TODAY() >= [Planned End Date]@row, 0, NETWORKDAYS(MAX(TODAY(), [Planned Start Date]@row), [Planned End Date]@row))

carlycrisp

@Paul Newcome Thank you so much! I really appreciate the help.

Paul Newcome

Happy to help. 👍️