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

DeeDee_Thompson
edited 05/08/25 in Formulas and Functions

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

Tags:

Best Answers

Answers

  • Adam Murphy
    Adam Murphy ✭✭✭✭✭✭
    Answer ✓

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

  • Adam Murphy
    Adam Murphy ✭✭✭✭✭✭
    Answer ✓

    Nice job, that looks like it will work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!