Sickness Occasions excluding weekends

[Cooki]

Im having trouble trying to exclude the weekdends from my sickness occasions formula

{=SUM(IF(FREQUENCY(IF(B1:B365>=(A1-365),IF(D1:D365="Sick",ROW(D1:D365))),IF(B1:B365>=(A1-365),IF(D1:D365<>"Sick",ROW(D1:D365)))),1))}

Sick database looks like

Date
01/01/19 Sick
02/01/19 Sick
03/01/19 Sick
04/01/19 Sick
05/01/19 Sick

Can covering it in an IF, tried Networkdays but none work

 

[Special-K99]

Try changing part of the the formula to

...IF(AND(D1:D365="Sick",WEEKDAY(D1:D365)<>1,WEEKDAY(D1:D365)<>6)...

 

[Cooki]

Try changing part of the the formula to

...IF(AND(D1:D365="Sick",WEEKDAY(D1:D365)<>1,WEEKDAY(D1:D365)<>6)...

Nope it dd not work

=SUM(IF(FREQUENCY(IF(AND(D1:D365="Sick",WEEKDAY(D1:D365)<>1,WEEKDAY(D1:D365)<>6)*B1:B365>=(A1-365),IF(D1:D365="Sick",ROW(D1:D365))),IF(B1:B365>=(A1-365),IF(D1:D365<>"Sick",ROW(D1:D365)))),1))

Getting #VALUE

 

[Special-K99]

Doesnt solve your problem but I just realsed this

WEEKDAY(D1:D365)<>6

should be

WEEKDAY(D1:D365)<>7

 

[Special-K99]

Another guess

=SUM(IF(FREQUENCY(IF(B1:B365>=(A1-365),IF((D1:D365="Sick")*(WEEKDAY(D1:D365)<>1)*(WEEKDAY(D1:D365)<>7),ROW(D1:D365))),IF(B1:B365>=(A1-365),IF(D1:D365<>"Sick",ROW(D1:D365)))),1))

 

[Tetra201]

Try

=SUM(IF(FREQUENCY(IF(B1:B365>=(A1-365),IF((D1:D365="Sick")*(WEEKDAY(B1:B365,2)<6),ROW(D1:D365))),IF(B1:B365>=(A1-365),IF((D1:D365<>"Sick")*(WEEKDAY(B1:B365,2)<6),ROW(D1:D365)))),1))

 

[Special-K99]

Got me columns wrong

Try changing part of the the formula to

...IF(AND(D1:D365="Sick",WEEKDAY(C1:C365)<>1,WEEKDAY(C1:C365)<>6)...

 

[Cooki]

YESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS

Finally been working on this for a long time and you have solved my issue

Thank you works like a dream

 

[Special-K99]

Well i dont know whose solution you're referring to but if its mine I still think mine is wrong

I think it should be this

...IF(AND(D1:D365="Sick",WEEKDAY(C1:C365)<>1,WEEKDAY(C1:C365)<>7)...

 

[Cooki]

Well i dont know whose solution you're referring to but if its mine I still think mine is wrong

I think it should be this

...IF(AND(D1:D365="Sick",WEEKDAY(C1:C365)<>1,WEEKDAY(C1:C365)<>7)...

Oh did not notice Tetra put a post up.

Tetra201 one works like a dream

Much appreciated to both of you taking the time to have a go