more then 1 day

UPN

Board Regular
Joined
May 14, 2006
Messages
138
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,986
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

Board Regular
Joined
May 14, 2006
Messages
138
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
 

UPN

Board Regular
Joined
May 14, 2006
Messages
138

ADVERTISEMENT

thanks

Thanks for the help
 

UPN

Board Regular
Joined
May 14, 2006
Messages
138
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

Board Regular
Joined
May 14, 2006
Messages
138
not sure

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

UPN

Board Regular
Joined
May 14, 2006
Messages
138
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

Board Regular
Joined
May 14, 2006
Messages
138
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,397
Messages
5,547,718
Members
410,807
Latest member
Coldesi
Top