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.


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! :)

  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Nov 4, 2022 at 17:42

1 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: enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.