# more then 1 day

#### UPN

##### Board Regular
I was wondering if there is a formula to check this out

if someone takes personal day it is 1 occurrance

but if they have more then 1 day in a row off it is 1 occurrance

example

4/24/06 personal day 1 occurrance

6/24/06 personal day
6/25/06 personal day

instead of these being 2 single occurrances it is consider 1 occurrance for the 2 days off in a row, but this can be more then 2 days also

so what i need to show is that the person only has 2 occurrance and not 3

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try:

=SUM(--ISNA(MATCH(A1:A10+1,A1:A10,0))*(A1:A10<>""))

Which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

forgot something on this

i forgot this part. the occurance are only counted if less then 90 days from curent date

example today date 9/23/06

8/25/06 would be an occurrance
5/25 would not be an occurrance

thanks

Thanks for the help

more

is there way to add more to this
if a cell contains an a, not to add this as an occurance.

in last example lets say a8 is 8/1/06 but in cell b8 there is an "A" so this is not added to the occurance cell (D1)

Perhaps

=SUM(--ISNA(MATCH(A1:A10+1,A1:A10,0))*(B1:B10<>"a")*(A1:A10>(TODAY()-90)))

confirmed with CTRL+SHIFT+ENTER.

What happens if you have 1 personal day on a Friday and another on the following Monday, is that 1 occurence or 2?

not sure

that is good question
I am not sure what happens if that does happens, will have to find that out

i am thinking that a personal day on friday and monday would be 1 occurance, which now wont work for the formula that was provided to me right?

question

How would i change the formula if a personal day on friday and another one on following Monday is considered one occurance.

Replies
8
Views
686
Replies
6
Views
184
Replies
21
Views
600
Replies
5
Views
310
Replies
2
Views
399

1,217,253
Messages
6,135,487
Members
449,942
Latest member

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