# Count of Consecutive WorkDays

#### tinmainiac

##### New Member
I am trying to create a formula in excel to count consecutive workdays . I have two columns, column A is Employee ID, column B is the date an employee had taken leave. What I am trying to do is identify those employees that have taken greater than 3 consecutive days off. I am playing with the IF and WORKDAY function but am having problems with the consecutive portion.

Any and all help is appreciated.

Bill

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Greg Truby

##### MrExcel MVP
1. Take a look at the Networkdays() function. If you don't have it, you need to use the Analysis Toolpak add-in. Note that it does differ from subtracting dates in that Networkdays(A1,A1) will return 1 not 0.
2. I'm not quite sure of your data layout yet. You might want to use Colo's HTML maker or another tool to give us an idea of the data you're working with.

#### tinmainiac

##### New Member
The current formula that I am playing with is this.

=IF(B8=(B7+1),IF(B8=(B6+2),IF(B8=(B5+3),"4"," ")," ")," ")

This works in that it identifies 4 consecutive days, the next issue is how to incorporate the WORKDAY function (or any other method) which would not count weekends.

I will put something up shortly with the data.

#### tinmainiac

##### New Member
the HTML maker is giving me an error, so I will list my data. Column A is my Employee ID, Col B is the Date leave was taken
A B
100181 7/5/2005
100181 7/6/2005
100181 7/7/2005
100181 7/8/2005
100181 7/11/2005

My nested IF statement (in the prior post) on 7/8/2005 will list a 4 as the fourth consecutive day. I also need to be able to identify that 7/11 was another consecutive workday as 7/9 and 7/10 are weekend days (Sat & Sun).

I Hope that helps to clarify.

#### barry houdini

##### MrExcel MVP
Do you want to count the consecutive workdays or just identify instances of 4 (or more) consecutive?

If it's the latter then it should be sufficient to check that the cell 3 back was 3 workdays ago, e.g. in C4 copied down

=IF(B4=WORKDAY(B1,3),"4","")

#### tinmainiac

##### New Member
That will give me what I need to at least identify the employees.

Thank you very much.

Bill

Replies
11
Views
366
Replies
3
Views
31
Replies
8
Views
332
Replies
1
Views
159
Replies
3
Views
283

1,191,686
Messages
5,988,073
Members
440,125
Latest member
vincentchu2369

### 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.

### Which adblocker are you using?

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

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