formula that determines how many working days 2 tasks were in progress at the same time.

DeeDee_Thompson

I have a Start and End Date for 2 tasks. (Task 1 and Task 2). [Task1 Start], [Task 1 End], [Task 2 Start], [Task 2 End]. I am trying to write a formula that determines how many working days both tasks were in progress at the same time.

Any suggestions would be great appreciated. thanks

Adam Murphy

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!

Adam Murphy

Nice job, that looks like it will work.

Adam Murphy

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!

DeeDee_Thompson

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}))

Adam Murphy

Nice job, that looks like it will work.

DeeDee_Thompson

Thanks @Adam Murphy