formula that determines how many working days 2 tasks were in progress at the same time.
Best Answers
-
Hi @DeeDee_Thompson, I would try something like =NETWORKDAYS(MAX([Start Date 1]@row, [Start Date 2]@row), MIN([End Date 1]@row, [End Date 2]@row))
Hope this helps!
-
Nice job, that looks like it will work.
Answers
-
Hi @DeeDee_Thompson, I would try something like =NETWORKDAYS(MAX([Start Date 1]@row, [Start Date 2]@row), MIN([End Date 1]@row, [End Date 2]@row))
Hope this helps!
-
Thank you @Adam Murphy it helped to point me in the right direction. However I had to use a couple of helper columns for the Start Date and End Date. See below.
Start Date
=IF(AND(ISDATE([Task 1 Start Date]@row), ISDATE([Task 2 Start Date]@row)), IF(NOT(ISDATE([Task 2 End Date]@row)), [Task 1 Start Date]@row, IF(MAX([Task 1 Start Date]@row, [Task 2 Start Date]@row) <= [Task 2 End Date]@row, MAX([Task 1 Start Date]@row, [Task 2 Start Date]@row))))
End Date
=IF(AND(ISDATE([Task 1 Start Date]@row), ISDATE([Task 2 Start Date]@row)), IF(MIN([Task 1 End Date]@row, [Task 2 End Date]@row) > [Task 2 Start Date]@row, MIN([Task 1 End Date]@row, [Task 2 End Date]@row)))
Number of days that both tasks were in progress on the same day
=IF(AND(ISDATE([Start Date]@row), ISDATE([End Date]@row)), NETWORKDAYS([Start Date]@row, [End Date]@row, {US Holidays Range - Holiday Dates}))
-
Nice job, that looks like it will work.
-
Thanks @Adam Murphy
Help Article Resources
Categories
Check out the Formula Handbook template!