Formula with conditions?

[deb0and]

I am struggling with a way to calculate some complex leave.

our sickness policy is as follows

in a 12 month period staff are only allowed 10 consecutive days off before they are put on an action plan OR 4 separate occasion within a 12 month period before they are put on an action plan.

I am trying to find to construct a formula that can do the below.

1) 10 consecutive days within a 12 month period
2) 4 separate occasions within a 12 month period

If both the above are achieved a way of alerting would be advantageous

What would be the best way of capturing this information? also i need a way to capture the dates of absence as well.

Thanks for your thoughts

[Domenic]

Can you provide or attach a sample of the data, along with the expected results?

[deb0and]

i have attached a sample. I am looking for the best way to capture the dates of sickness. This is what is throwing me at the moment.

[tmanbone]

Here's one way to start looking at it. I suggested my own way of handling the off days, as you were uncertain about it.

If you're ok with this data pattern, there's one more thing left to do: make sure the formula in column E updates accordingly, when there's less than 4 occasions (e.g. 3) and one of them is more than 10 days (meaning, the formula would have output the same thing for all the 3 rows of that person).

You'll also have to tweak the Days column to account for the year start and end (for when a person is on leave before the year end, and comes back after).

[Domenic]

Here's another way... In the attached sample file, you'll notice that for simplicity I entered the dates for the entire year but only included data for a few months. You'll also notice that sick days are indicated by entering an 'S' for the relevant date. Note that I assumed that the relevant days are Monday through Friday. So this means that if someone is sick on Friday and the following Monday, it's considered as one instance.

[deb0and]

Domenic,

That's pretty much what I am looking for except our week is a full seven days. Could the formula incorporate this?

Many Thanks

Andy

[Domenic]

Try...

B4, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=SUM(IF(FREQUENCY(IF(B$9:B$373="S",ROW(B$9:B$373)),IF(B$9:B$373<>"S",ROW(B$9:B$373))),1))

B5, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=SUM(IF(FREQUENCY(IF(B$9:B$373="S",ROW(B$9:B$373)),IF(B$9:B$373<>"S",ROW(B$9:B$373)))>=10,1))

[deb0and]

mmm.... I have tried this and its throwing out an error, maybe i am doing something wrong. I have attached what i have done.

Thanks for help, much appreciated.

[Domenic]

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, instead of just ENTER. In other words, press down both the CONTROL and SHIFT keys, then while both keys are pressed down, press the ENTER key. If done correctly, Excel will automatically place curly braces {...} around the formula.

[deb0and]

Oh i was doing it wrong, thanks for that got it working now.