CODEWITHSUNDEEP
google-sheetscountif
User1291
User1291

Reputation: 8182

COUNTIFS date in row is a workday

Ok, so what I'm trying to do is for each month count the number of workdays, minus public holidays.

B2     -- the current year
E2:E13 -- the first day of each month
F2:F13 -- the amount of days (workday or otherwise) in that month, that year
G2:G13 -- the amount of WORK days in that month (i.e. the formula I'm trying ot write)
A8:A   -- the public holidays

I can take these and create a formula (here for G2)

=NETWORKDAYS(CONCATENATE("1.",MONTH(E2),".",$B$2),CONCATENATE(F2,".",MONTH(E2),".",$B$2), $A$8:A)

(which leverages the date format dd.mm.yyyy)

The problem with that is that some of these holidays are only half a day off, whereas this formula will give people the full day off.

So an approach could be to simply add half a day back in for every half day off that was wrongfully counted as a full day off, that month.

Assuming I have

C8:C   -- is the holiday in the A cell of the same row a half-day?

That turns our formula into

=NETWORKDAYS(CONCATENATE("1.",MONTH(E2),".",$B$2),CONCATENATE(F2,".",MONTH(E2),".",$B$2), $A$8:A)+COUNTIFS($A$8:A,">"&EOMONTH(E2,-1),$A$8:A,"<"&EOMONTH(E2,0)+1,$C$8:C,TRUE)*0.5

(EDIT: example sheet)

But now here's the problem: that holiday may fall on a weekend.

So if a holiday that happens to be half a day off accidentally lies on a Sunday, say, we've just wrongfully added an extra half day of work.

What I need is an additional condition that will evaluate to "true" iff the holiday date is a workday, something along

[...]+COUNTIFS([...], $A$8:A, NETWORKDAYS(ROW,ROW) > 0)*0.5

I have no idea how to reference the row like that, though.

The easy way out would be a helper column that would calculate this boolean for me, but that's ugly as unlike the 'half-day' column, we do not want the user to be able to modify these values so having them see the column will just confuse them.)

How do I write the condition in the countifs?

Upvotes: 0

Views: 347

Answers (2)

fullfine
fullfine

Reputation: 1461

I have found a simple solution without using countifs. The result you are looking for is the sum of three values:

  • Result = Working days in a month - Full holidays days - Half holidays days * 0.5

To do this, we will start by calculating the workdays in a month, the workdays minus full holidays and the workdays minus half holidays

  • WD: =NETWORKDAYS( CONCATENATE("1.",MONTH(E5),".",$B$2), CONCATENATE(CONCATENATE(F5,".",MONTH(E5),".",$B$2))
  • WD_FULL_H :=NETWORKDAYS( CONCATENATE("1.",MONTH(E5),".",$B$2), CONCATENATE(F5,".",MONTH(E5),".",$B$2), filter(A8:A,C8:C=FALSE)))
  • WD_HALF_H: =NETWORKDAYS( CONCATENATE("1.",MONTH(E5),".",$B$2), CONCATENATE(F5,".",MONTH(E5),".",$B$2), filter(A8:A,C8:C=TRUE)).

In this way we make sure that any vacation that coincides with a public holiday does not alter the final result. Next, we calculate the final working days. To do this we need to get how many full and half holidays we have in the month, which we get by subtracting the number of working days in the month:

  • FINAL WD: WD - (WD - WD_FULL_H) - (WD - WD_HALF_H) * 0.5

Simplifying:

  • FINAL WD: WD_FULL_H - (WD - WD_HALF_H) * 0.5

Finally, without auxiliary calculations:

  • FINAL WD:
=NETWORKDAYS(
CONCATENATE("1.",MONTH(E5),".",$B$2),
CONCATENATE(F5,".",MONTH(E5),".",$B$2), 
filter(A8:A,C8:C=FALSE)) 
-
(NETWORKDAYS(
CONCATENATE("1.",MONTH(E5),".",$B$2),
CONCATENATE(F5,".",MONTH(E5),".",$B$2))) 
-
NETWORKDAYS(
CONCATENATE("1.",MONTH(E5),".",$B$2),
CONCATENATE(F5,".",MONTH(E5),".",$B$2), 
filter(A8:A,C8:C=TRUE)))
*0.5

Upvotes: 1

MattKing
MattKing

Reputation: 7773

UPDATED FORMULA:

I think this should do it. though there are probably other, maybe even tidier ways.

It adds back in half days if it finds matches in a FILTER().

=ARRAYFORMULA(NETWORKDAYS(E2,EOMONTH(E2,0),A$8:A$18)+IFERROR(FILTER(C$8:C$18,EOMONTH(A$8:A$18,0)=EOMONTH(E2,0),C$8:C$18,WEEKDAY(A8:A18,2<6))/2))

Upvotes: 1

Related Questions