Logic formula direction - complex equation based off COUNTIFS

%3CLINGO-SUB%20id%3D%22lingo-sub-2445373%22%20slang%3D%22en-US%22%3ELogic%20formula%20direction%20-%20complex%20equation%20based%20off%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2445373%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%20I'm%20looking%20for%20some%20direction%20on%20how%20to%20build%20a%20logic%20formula%20in%20the%20attached%20file%20that%20will%20find%20the%20specific%20shifts%20that%20went%20over%20the%20scheduled%20time.%20The%20HR%20system%20at%20my%20company%20has%20a%20glitch%20that%20sums%20the%20total%20hours%20worked%20by%20the%20count%20of%20shifts%20worked%20when%20pulling%20a%20schedule%20report%20(so%20if%20you%20worked%20a%20total%20of%206%20hours%20across%203%20shifts%2C%20it%20would%20say%20you%20worked%2018%20hours%20for%20the%20day)%2C%20and%20this%20is%20causing%20issues%20with%20reporting%20on%20what%20shifts%20were%20worked%20over%20schedule%2Fwhen%2Fwhere.%20I'm%20familiar%20with%20nesting%20logic%20formulas%2C%20but%20I've%20been%20stumped%20for%20the%20past%20two%20weeks%20on%20how%20exactly%20to%20build%20out%20what%20I%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20N%20already%20tells%20when%20the%20conditions%20are%20partially%20necessary%20for%20the%20logic%20formula%2C%20but%20the%20logic%20formula%20I%20need%20also%20has%20to%20take%20into%20account%20the%20specific%20day%20of%20the%20shifts%20in%20question%20(Rows%202-6%20are%20the%20same%20worker%20but%20different%20days%20although%20Column%20N%20values%20are%20all%20TRUE).%20Column%20G%20is%20the%20sum%20by%20count%20issue...%20I%20am%20trying%20to%20use%20Column%20H%20and%20F%20to%20resolve%20the%20issues%20in%20G.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20in%20the%20demo%20file%20attached%2C%20Row%202%20is%20the%20first%20entry%20for%20John%20Smith%20-%20the%20first%20shift%20is%207%20hours%20off%20his%20total%2010.6%20hours%20worked%2C%20so%20H2%20returns%20a%20positive%20value%20because%20he%20worked%20the%20full%207%20hours.%20The%20second%20shift%2C%20Row%203%2C%20returns%20a%20negative%20value%20because%20H2%20is%20greater%20than%20F3%2C%20thereby%20indicating%20that%20it%20was%20that%20shift%20that%20caused%20John%20Smith%20to%20work%20beyond%20his%20scheduled%20hours.%20Similarly%2C%20Rows%204-6%20follow%20this%20pattern.%20H4-6%20are%20all%20positive%2C%20indicating%20that%20John%20Smith%20did%20not%20work%20over%20his%20scheduled%20time%20on%20any%20shift.%20The%20last%20row%2C%20Row%207%20with%20Mary%20Johnson%2C%20did%20see%20her%20work%20over%20her%20shift%20with%20H7%20yielding%20a%20negative%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20H%20is%20manually%20calculated%20in%20the%20attached%20spreadsheet%20with%20Columns%20K%2FL%20automatically%20calculating%20the%20values%20I%20need%20for%20reporting.%20What%20I'm%20looking%20for%20is%20direction%20for%20Column%20H...%20Based%20off%20the%20values%20in%20Columns%20B%2FC%2FJ...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EIf%20B%2FC%2FJ%20%26gt%3B%201%20and%20it's%20the%20first%20instance%20of%20B%2FC%2FJ%20and%20J(row)%26lt%3B%26gt%3BJ(above)%2C%20subtract%20F%20from%20G...%3C%2FLI%3E%3CLI%3EIf%20B%2FC%2FJ%20%26gt%3B%201%20and%20it's%20not%20the%20first%20instance%20of%20B%2FC%2FJ%20and%20J(row)%3DJ(above)%2C%20subtract%20H%20from%20F...%3C%2FLI%3E%3CLI%3Eif%20B%2FC%2FJ%20%26gt%3B%201%20and%20it's%20the%20last%20instance%20of%20B%2FC%2FJ%20and%20J(row)%3DJ(above)%2C%20subtract%20F%20from%20H%3C%2FLI%3E%3CLI%3Eif%20B%2FC%2FJ%20%26lt%3B%201%20and%20G%20%26gt%3B%20F%2C%20subtract%20F%20from%20G%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20nest%20a%20logic%20formula%20for%20if%20B%2FC%2FJ%20%26gt%3B%201%20and%20J(row)%3DJ(above)%2Fetc...%20but%20I%20do%20not%20know%20how%20to%20build%20a%20formula%20that%20looks%20at%20the%20first%2Flast%2Fnot-first-or-last%20instance%20of%20B%2FC%2FJ%20%26gt%3B%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20support%20or%20direction%20is%20appreciated!%20Believe%20me%20-%20this%20is%20easier%20than%20going%20to%20the%20HR%20system%20developer%20and%20having%20them%20deglitch%20the%20system.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2445373%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2627347%22%20slang%3D%22en-US%22%3ERe%3A%20Logic%20formula%20direction%20-%20complex%20equation%20based%20off%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2627347%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914627%22%20target%3D%22_blank%22%3E%40jaylaplante%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20the%20shift%20ended%20at%2011%3A30%20on%205%2F23%2F2021%20how%20can%20the%20next%20shift%20be%20starting%20at%2011%3A00%20on%205%2F23%2F2021%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2628227%22%20slang%3D%22en-US%22%3ERe%3A%20Logic%20formula%20direction%20-%20complex%20equation%20based%20off%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2628227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914627%22%20target%3D%22_blank%22%3E%40jaylaplante%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20I%20understand%20the%20logic%20even%20allowing%20for%20the%20overlapping%20shift.%26nbsp%3B%20What%20I%20have%20done%20is%20illustrate%20a%20formula%20that%20accumulates%20the%20hours%20worked%20within%20the%20day%2C%20in%20the%20hope%20that%20it%20might%20be%20something%20you%20could%20use.%26nbsp%3B%20%3CEM%3EBe%20careful%20if%20your%20table%20has%201000s%20of%20rows%20because%20the%20formula%20becomes%20computationally%20expensive.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello - I'm looking for some direction on how to build a logic formula in the attached file that will find the specific shifts that went over the scheduled time. The HR system at my company has a glitch that sums the total hours worked by the count of shifts worked when pulling a schedule report (so if you worked a total of 6 hours across 3 shifts, it would say you worked 18 hours for the day), and this is causing issues with reporting on what shifts were worked over schedule/when/where. I'm familiar with nesting logic formulas, but I've been stumped for the past two weeks on how exactly to build out what I need.

 

Column N already tells when the conditions are partially necessary for the logic formula, but the logic formula I need also has to take into account the specific day of the shifts in question (Rows 2-6 are the same worker but different days although Column N values are all TRUE). Column G is the sum by count issue... I am trying to use Column H and F to resolve the issues in G.

 

Basically, in the demo file attached, Row 2 is the first entry for John Smith - the first shift is 7 hours off his total 10.6 hours worked, so H2 returns a positive value because he worked the full 7 hours. The second shift, Row 3, returns a negative value because H2 is greater than F3, thereby indicating that it was that shift that caused John Smith to work beyond his scheduled hours. Similarly, Rows 4-6 follow this pattern. H4-6 are all positive, indicating that John Smith did not work over his scheduled time on any shift. The last row, Row 7 with Mary Johnson, did see her work over her shift with H7 yielding a negative number.

 

Column H is manually calculated in the attached spreadsheet with Columns K/L automatically calculating the values I need for reporting. What I'm looking for is direction for Column H... Based off the values in Columns B/C/J...

 

  • If B/C/J > 1 and it's the first instance of B/C/J and J(row)<>J(above), subtract F from G...
  • If B/C/J > 1 and it's not the first instance of B/C/J and J(row)=J(above), subtract H from F...
  • if B/C/J > 1 and it's the last instance of B/C/J and J(row)=J(above), subtract F from H
  • if B/C/J < 1 and G > F, subtract F from G

 

I can nest a logic formula for if B/C/J > 1 and J(row)=J(above)/etc... but I do not know how to build a formula that looks at the first/last/not-first-or-last instance of B/C/J > 1.

 

Any support or direction is appreciated! Believe me - this is easier than going to the HR system developer and having them deglitch the system.

4 Replies

@jaylaplante 

 

if the shift ended at 11:30 on 5/23/2021 how can the next shift be starting at 11:00 on 5/23/2021?

@jaylaplante 

I am not sure I understand the logic even allowing for the overlapping shift.  What I have done is illustrate a formula that accumulates the hours worked within the day, in the hope that it might be something you could use.  Be careful if your table has 1000s of rows because the formula becomes computationally expensive.

It's how they're scheduled, so in that instance, the employee picked two shifts that overlapped to get more hours, which is half the issue in tracking things.

I ended up reworking the report with our IT Team to get what we needed, which, although it's very clunky, more or less resolved the issue.
Thank you - I may actually use that formula in the future. I ended up working with our IT Team to rework the report, and it has worked so far.