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

Caleb W

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.

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.