# Sickness Occasions excluding weekends

#### Cooki

##### Board Regular
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try changing part of the the formula to

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

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

Doesnt solve your problem but I just realsed this

Code:
``WEEKDAY(D1:D365)<>6``

should be

Code:
``WEEKDAY(D1:D365)<>7``

Last edited:
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))

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

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)...

YESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS

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

Thank you works like a dream

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)...

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

Replies
1
Views
324
Replies
13
Views
836
Replies
2
Views
113
Replies
0
Views
224
Replies
2
Views
465

1,219,791
Messages
6,150,286
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back