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

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

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.