more then 1 day

[UPN]

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

 

[Oaktree]

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)

 

[UPN]

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

 

[Oaktree]

Try:

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

Book1
	A	B	C	D
1	8/25/2006			3
2	8/26/2006
3	8/27/2006
4	8/28/2006
5	9/20/2006
6	9/21/2006
7	9/23/2006
8
9
10
Sheet1

 

[UPN]

thanks

Thanks for the help

 

[UPN]

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)

 

[barry houdini]

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?

 

[UPN]

not sure

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

 

[UPN]

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?

 

[UPN]

question

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

Thanks in advance for help