Help with a formula in my working schedule

Question

Background info:

I have a work schedule i need help with. I made a TEST sheet that represents the real sheet. at this time I have no way of rebuilding the sheet "from the ground".

There's a formula in this sheet i need help with, it's in sheet " Workhours statistics " marked in blue in cell B3.

https://docs.google.com/spreadsheets/d/1a2PnPfqr3nU_AhtqOO05V5AL2c34aQnr4i3mOLILwUA/edit?usp=sharing

This is how the sheet works today; I have a schedule with all the employee names, dates for each workday, areas that they work in, and their start times.

Each of the cells on a given date counts as 8 hours so 8 hours = full workday. But if they call in sick that date or the night before i will type in "SICK" and they will not be counted.

For example in sheet ": |2022-09-13 = 40 workhours. |2022-09-14= 32 workhours|

Now to the problem:

Lets say for example a employee comes to me with sickpapers. and will work only a given percentage of a full work day (fully workday is 8 hours) and will instead work 75% of those hours. meaning 6 hours for a period of time. (for 1 whole month for instance). After this he/she works 8 hours aday again.

I would like the formula in " Workhours statistics " sheet to deduct these 2 hours as "-2" each day until the sickleave time has passed.

Im half way with this formula because it only counts 1 person at a time. i want the formula too look at the whole columns in sheet "Sickleave log" a:a, b:b and d:d.

Im aware that i could just copy this formula and do formula+formula+formula in the cell but this sickleave-list will grow to big for that.

(And the sickleave log is constant because i need the history saved)

I hope you'll understand what im asking for :) Your help is appreciated! :)

Answer 1

I created a copy of Sickleave log just to change some numbers and try.

First, I think if it's historic, you should add a column that indicates when from is the sickleave periode. With that criteria, I created column B in that copy tab, and created this formula that compares Workhours Statistics!B1 (so, the first date) with B and C columns from each row of the sickleave log, then multiplies 8 by the Sick Percentage and summed all those values:

=-1*SUM(ARRAYFORMULA(IF((B1>='Kopia av Sickleave log'!B2:B) + (B1<='Kopia av Sickleave log'!C2:C) = 2;8*'Kopia av Sickleave log'!D2:D;0)))

Now, in order to make it expandable to all the columns of the Stats tab, you can insert it in a BYCOL formula, like this:

=bycol(B1:1;lambda(each;-1*SUM(ARRAYFORMULA(IF((each>='Kopia av Sickleave log'!B2:B) + (each<='Kopia av Sickleave log'!C2:C) = 2;8*'Kopia av Sickleave log'!D2:D;0)))))

See it working in B6 from your Workhour Statistics:

What do you think??, does this solve your issue??