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
 

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)
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
not sure

that is good question
I am not sure what happens if that does happens, will have to find that out
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top