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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,126
Messages
5,857,521
Members
431,883
Latest member
Hien

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
Top