Count of Consecutive WorkDays

tinmainiac

New Member
Joined
Oct 12, 2006
Messages
7
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
  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
Joined
Oct 12, 2006
Messages
7
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
Joined
Oct 12, 2006
Messages
7
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Oct 12, 2006
Messages
7
That will give me what I need to at least identify the employees.

Thank you very much.

Bill
 

Forum statistics

Threads
1,136,991
Messages
5,679,014
Members
419,799
Latest member
APInfa

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