Jun 14 2021 08:25 AM
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...
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.
Aug 09 2021 05:49 AM
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?
Aug 09 2021 09:34 AM
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.
Aug 10 2021 06:50 AM
Aug 10 2021 06:51 AM