I am trying to count days that an employee works on specific tasks in set periods.

Right now I am tracking an employee's total amount of days working with a specific scope of work. I am using our company master schedule to pull data from, then a support sheet that is set up with each of the employee's names to track. It works smooth except when a project over laps my specific dates ranges. I need to count from 01/01 - 06/30 and then a separate count for 07/01-12/31 each year.

I am using this formula in my support sheet "=SUMIFS({Days on Site}, {Scope}, "Precast Install", {Start}, >=DATE(2024, 6, 31), {Start}, <=DATE(2024, 12, 31), {Schedule Range 1}, [Project Leader Name]@row)"

But I am stumped on how to get it to transition to the next set of dates when th eprojects runs through that date overlap.

Find more posts tagged with

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Caleb W

I added three helper columns to the demo sheet below to cope with the overlap.

[Days on Site] =NETWORKDAY(Start@row, End@row)
[Days on Site in Range] =IF(Overlap@row, NETWORKDAY(MAX(Start@row, S#), MIN(End@row, E#)))
[Overlap] =OR(AND(Start@row < S#, End@row >= S#), AND(Start@row >= S#, End@row > E#))
[Start in Range] =AND(Start@row >= S#, Start@row <= E#)

First, the [Overlap] column determines if the duration between the Start and End is within the range defined by S# and F# in the Sheet Summary fields. For example, Project 2 starts before the S# and ends after the S#, so the project has an overlap period.

Second, the [Days on Site in Range] column calculates the days within the range. For example, Project 2 calculates the NETWORKDAY using the S# or 07/01/24 as the start date by using the MAX(Start@row, S#) formula and uses the End@row or 08/15/24 as the end date by using the MIN(End@row, E#) formula.

The [Start in Range] is the same as the condition {Start}, >DATE(2024, 6, 31), {Start}, <=DATE(2024, 12, 31).

https://app.smartsheet.com/b/publish?EQBCT=98907d32c1f6471f9526daa098614626

Using the values in the helper columns, the [John D : 07/01-12/31 - Overlap]# field in the Sheet Summary caliculate the days John D worked on Precast Install within the range with the following formula;

=SUMIFS([Days on Site in Range]:[Days on Site in Range], Scope:Scope, "Precast Install", Employee:Employee, "John D", Overlap:Overlap, true) + SUMIFS([Days on Site]:[Days on Site], Scope:Scope, "Precast Install", Employee:Employee, "John D", Overlap:Overlap, false, [Start in Range]:[Start in Range], true)

The first SUMIFS sums the [Days on Site in Range] if the Overlap is true and other conditions are satisfied. The second SUMIFS sums the [Days on Site] if the Overlap is false, [Start in Range] is true, and other conditions are satisfied.

Caleb W

Thank you @jmyzk_cloudsmart_jp!! Can this be implemented into my master schedule that can feed into my support sheet I have above?

Here is my master schedule

Right now since the precast install starts on 12/02/24 it is counting all 25 days in that time frame where I would need it to stop counting at 12/31 and start again January 1.

jmyzk_cloudsmart_jp

@Caleb W

I added those three helper columns to the demo master schedule sheet (Scope of Work.)

[S Half] =YEAR(Start@row) + "-" + IF(MONTH(Start@row) < 7, 1, 2)
[F Half] =YEAR(Finish@row) + "-" + IF(MONTH(Finish@row) < 7, 1, 2)
[Duration in Half] =IF([S Half]@row <> [F Half]@row, IF(MONTH(Start@row) < 7, NETWORKDAY(Start@row, MIN(Finish@row, DATE(YEAR(Start@row), 6, 31))), NETWORKDAY(Start@row, MIN(Finish@row, DATE(YEAR(Start@row), 12, 31)))))

https://app.smartsheet.com/b/publish?EQBCT=741e11048ef2457eb4f3294b525aae66

Then, I used the cell formula to dynamically calculate the S/F half data for the parent rows, such as 2024-1 and 2024-2.

[Jason Merz & 2024-1]
=SUMIFS({Scope of Work : Duration}, {Scope of Work : Assigned To}, $[Assigned To]2, {Scope of Work : Scope of Work}, SOW#, {Scope of Work : S Half}, [2024-1]$1, {Scope of Work : F Half}, [2024-1]$1)
+ SUMIFS({Scope of Work : Duration in Half}, {Scope of Work : Assigned To}, $[Assigned To]2, {Scope of Work : Scope of Work}, SOW#, {Scope of Work : S Half}, [2024-1]$1)

https://app.smartsheet.com/b/publish?EQBCT=417f95f6deed48e19a3203787cb69e86

This approach may be easier to manage, as you just need to add additional S/F Half columns as the years pass.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.