# Count Consecutive Days while bridging over days off and holidays

1. ## Count Consecutive Days while bridging over days off and holidays

Hi, I'm trying to write a formula to count consecutive days that an employee has called off to work that also bridges over vacation days and days off.

For example, I have an employee who calls in under "fmla" on Monday, was "scheduled off" on Tuesday, and then is "MIA" on Wednesday, the formula should see the fmla and mia segments as consecutive and ignore the day off that appears as either "day off", "holiday" or "vacation" in the cell for Tuesday.

2. ## Re: Count Consecutive Days while bridging over days off and holidays

If you post a workbook with some sample data and the results you expect it will be much easier to help you.

3. Originally Posted by Jacc
If you post a workbook with some sample data and the results you expect it will be much easier to help you.
This site is blocked at work so I sent it to my phone to upload it, hope this works!

4. ## Re: Count Consecutive Days while bridging over days off and holidays

I'll get back to this in one day.

5. ## Re: Count Consecutive Days while bridging over days off and holidays

Please explain the reference to 'fmla' in your original and where this relates in the uploaded workbook.

6. ## Re: Count Consecutive Days while bridging over days off and holidays

I think fmla is just another type of leave that would show up as red in his chart. https://en.wikipedia.org/wiki/Family...ve_Act_of_1993

7. ## Re: Count Consecutive Days while bridging over days off and holidays

This is quite a strange calendar. It starts on Wednesday and it appears that Sunday is the only day when there is no activity. In addition, how are future dates added to this?

8. ## Re: Count Consecutive Days while bridging over days off and holidays

I'm mostly thinking about the actual algorithm, the rest can be fixed. In my mind it has to loop around a lot to test for all combinations of consecutive days. And the IF's for the green days and so on. One of those problems that look innocent at first glance and then blow up in your face.

9. ## Re: Count Consecutive Days while bridging over days off and holidays

We're better able to advise on the algorithm if we know exactly how your end result is calculated. Hence my question seeking to know which cells contribute to your result. It may well be that you don't need a macro or UDF at all. Often standard functions combined in a complex formula are sufficient.

10. ## Re: Count Consecutive Days while bridging over days off and holidays

I have written a simple User Defined Function (UDF) called CONSEQOFF() that you can install into our workbooks as needed and it will provide the new count function you need.
``Please Login or Register  to view this content.``
After it installed, it is used in a cell like any other function.

=CONSEQOFF(Workdays)

Workdays = the range of cells you want to evaluate

=========
How to install the User Defined Function:

2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.

11. ## Re: Count Consecutive Days while bridging over days off and holidays

If I understand this problem correctly, cannot HOLOFF, Dayoff and Blank be considered the same thing and give all of those say a value of 0. Then, consider all of the others (MIA, MIAOUT and UPTO) the same and give them a value of 1. It appears to me, that no matter how many days are worked or days were off, the answer will be the sum of the 1s. I can't see anything in the example to give me a different slant on it but it seems to be too simple to be correct.

Row 5 as I understand the rules should be 3 and not 2.

I created a lookup table in A19:B24 assigning the 1s and 0s for the different leaves, holidays and work days.

I created a separate table using VLOOKUP and directly referenced the example table using the following:
Formula:
`Please Login or Register  to view this content.`

Then it was a matter of summing the 1s in each row:
Formula:
`Please Login or Register  to view this content.`

12. ## Re: Count Consecutive Days while bridging over days off and holidays

Originally Posted by newdoverman
...cannot HOLOFF, Dayoff and Blank be considered the same thing and give all of those say a value of 0.

No, HOLOFF and DAYOFF would not reset the "counting" of other non-planned days off, would not reset the count to zero.
BLANK resets the count to zero in terms of consecutive days off since the person worked.

Row 5 would be 2 as G:H are the counted cells.

13. ## Re: Count Consecutive Days while bridging over days off and holidays

In the example file it looks like hardly anyone worked. I'm sure this is not the case and the OP has just exaggerated how the data might appear, though it is a bit misleading.

Pete

EDIT:

@Jerry,

you will need to take account of this FMLA leave, which is not shown in the example (yes, I can see where it should go, but the OP might not).

Pete

14. ## Re: Count Consecutive Days while bridging over days off and holidays

It looks like the work schedule for patients at a nursing home

15. ## Re: Count Consecutive Days while bridging over days off and holidays

Though the logic appears valid and it yields the expected results I am not particularly confident of this one.

Array entered in K2 / filled down.

=SUM(IFERROR(((TRANSPOSE(C2:H2)={"MIAOUT","MIA","UPTO"})+(-1*(TRANSPOSE(D2:H2)={"","<>DAYOFF","<>HOLOFF"}))),FALSE))+1

Edit: TRANSPOSE was unnecessary. Loose ends from the build. My bad. This instead.

=SUM(IFERROR(((C2:H2={"MIAOUT";"MIA";"UPTO"})+(-1*(D2:H2={"";"<>DAYOFF";"<>HOLOFF"}))),FALSE))+1

 Row\Col A B C D E F G H I J K 1 Name Max Consecutive Days 1-Jul 2-Jul 3-Jul 4-Jul 6-Jul 7-Jul Expected Results in column B 2 Bob Jones 6 MIAOUT DAYOFF MIAOUT MIAOUT MIAOUT MIAOUT 5 5 3 John Smith 6 MIAOUT MIAOUT MIAOUT DAYOFF MIAOUT MIAOUT 5 5 4 Bill Joe 6 HOLOFF MIA MIA MIA MIA MIA 5 5 5 George Washington 3 HOLOFF UPTO DAYOFF UPTO MIA 2 2 6 Thomas Jefferson 6 UPTO UPTO UPTO DAYOFF UPTO UPTO 5 5 7 Ben Franklin 6 MIAOUT DAYOFF MIAOUT HOLOFF MIA MIA 4 4 8 Sam Adams 6 DAYOFF UPTO UPTO HOLOFF UPTO UPTO 4 4 9 John Hamilton 5 DAYOFF UPTO HOLOFF UPTO UPTO 3 3

16. ## Re: Count Consecutive Days while bridging over days off and holidays

The above wasn't working. When column H items were changed to DAYOFF for example the results wouldn't budge. (I thought I'd checked everything out.)

It works with this one.

=SUM(IFERROR(((C2:H2={"MIAOUT";"MIA";"UPTO"})+(-1*(C2:H2={"";"<>MIAOUT";"<>MIA";"<>UPTO"}))),FALSE))

It was the offsetting ranges causing the goof. The DAYOFF and HOLOFF were unnecessary, too.

17. ## Re: Count Consecutive Days while bridging over days off and holidays

Here's another.

=MAX(FREQUENCY(IF(C2:I2={"MIAOUT";"MIA";"UPTO"},COLUMN(C2:I2)),IF(C2:I2={"";"<>MIAOUT";"<>MIA";"<>UPTO"},COLUMN(C2:I2))))

Array entered.

18. ## Re: Count Consecutive Days while bridging over days off and holidays

Very impressive by FR and Jerry, I'm not good at this kind of problem. Just for kicks I put in some random to test the functions against each other. I then realized that I don't even understand the original problem logic to start with so I have no clue what's right or wrong. I hope OP shows up soon.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1