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+IF(WEEKDAY(A1:A10,2)=5,3,1),A1:A10,0))*(B1:B10<>"a")*(A1:A10>(TODAY()-90)))}

But, this is going to open a whole new can of worms with holidays. What if Monday is a holiday? Then do you count until Tuesday? What if Tuesday is a holiday? Do you count Monday and Wednesday as one occurrence? Since you cannot use WORKDAY as an array formula, I'm not sure how you would do this without adding a new column...

 

[UPN]

thanks

I am not sure, questions I will have to ask.

Thanks for the help reallyappreciate it

 

[UPN]

answers

personal day on friday and following monday is 1 occurrence not 2

Personal days before and after holiday is 1 occurrence

so now really opens the can of worms

 

[UPN]

Oaktree

Oaktree

What do i do now?

 

[Oaktree]

I think you'd have to add another column with

B1=--ISNA(MATCH(WORKDAY(A1,1,$H$1:$H$25),$A$1:$A$100,0))

Where A1:A100 is your list of dates and H1:H25 has your list of holidays (stored as Excel dates). The sum of column B will be the total number of "occurrences".

Edit: corrected error in formula that had C's instead of H's

 

[mortgageman]

Try:

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

But, this is going to open a whole new can of worms with holidays. What if Monday is a holiday? Then do you count until Tuesday? What if Tuesday is a holiday? Do you count Monday and Wednesday as one occurrence? Since you cannot use WORKDAY as an array formula, I'm not sure how you would do this without adding a new column...

I have been lurking here, watching with amazement as you come up with more complex formulas each time a new wrinkle is added. However, why did the Friday - Monday issue open up a whole new can of Holiday worms. Wasn't the Holiday issue there before the weekend issue came up. I mean what if someone is off on Wednesday, Thursday is Thanksgiving and is off on Friday?

Gene, "Looking for trouble", Klein

 

[UPN]

thanks

the last formula you gave me is it to replace the other one, or do i need them both?

 

[Oaktree]

the last formula you gave me is it to replace the other one, or do i need them both?

I got carried away and forgot about all the other rules.

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

Copied down then sum column C. You shouldn't need the other formulas, as there wasn't a way (at least, I am not aware of a way to) incorporate the WORKDAY formula into the array formula as before.

 

[UPN]

thanks

Well hopefully the holiday thing will never happen, but there is always that one person that throws a monkey wrench into everything, and then thigns come to quick stop...LOL

 

[Oaktree]

I have been lurking here, watching with amazement as you come up with more complex formulas each time a new wrinkle is added. However, why did the Friday - Monday issue open up a whole new can of Holiday worms. Wasn't the Holiday issue there before the weekend issue came up. I mean what if someone is off on Wednesday, Thursday is Thanksgiving and is off on Friday?

Gene, "Looking for trouble", Klein

Hi, Gene. The "can of Holiday worms" I was referring to is that it doesn't appear that WORKDAY can be used in an array formula. So, the one-stop-shop approach to a formula hit a road block when we started introducing the caveat that the day before/day after a holiday is counted as one occurrence. Hopefully the extra column workaround is sufficient for UPN's needs.