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+ISNUMBER(MATCH(A1:A10+1,H:H,0))+(A1:A10=("11/22/2006"+0))),A1:A10,0))*(A1:A10<>"")*(A1:A10>(TODAY()-90)))}

Where H:H is your list of holidays as before...

 

[UPN]

sorry

i forgot about christmas eve and chritmas day as well as new year's eve and new year s day

 

[Oaktree]

Same idea...

{=SUM(--ISNA(MATCH(A1:A10+(1+ISNUMBER(MATCH(A1:A10+1,H:H,0))+(A1:A10=("11/22/2006"+0))+(A1:A10=("12/23/2006"+0))+(A1:A10=("12/30/2006"+0))),A1:A10,0))*(A1:A10<>"")*(A1:A10>(TODAY()-90)))}

 

[UPN]

thanks

thanks for the help, sorry i am such a nuisance

 

[UPN]

I am trying to use the older ones and and they dont seem to be working right.

 

[Oaktree]

What do you mean by "older ones" and "dont seem to be working right"?

 

[UPN]

from the earlier post

with the people working on weekend, if i put 10/14 in a cell and 10/15 in another and it counts it as 2 occurrances when it should be 1

 

[Oaktree]

Which formula are you using? Are you using CTRL+SHIFT+ENTER?

 

[UPN]

yes on the contrl+shift+enter

was trying to use

Quote:
C1=--AND(ISNA(MATCH(WORKDAY(A1,1,$H$1:$H$25),$A$1:$A$100,0)),B1<>"a",A1>(TODAY()-90))

 

[Oaktree]

That was designed to exclude Saturdays and Sundays. Since Oct 14 is a Saturday, the formula fails.

Go back farther to Barry's formula on page 1 of this thread:

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