Count of Consecutive WorkDays

[tinmainiac]

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

 

[Greg Truby]

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]

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]

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]

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]

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

Thank you very much.

Bill