+ Reply to Thread
Results 1 to 18 of 18

Count Consecutive Days while bridging over days off and holidays

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    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.

    I tried adding an or statement to the frequency function with no sucess, any ideas? Please help!

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    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.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    04-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2
    Quote Originally Posted by Jacc View Post
    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!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

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

    I'll get back to this in one day.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    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. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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?
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    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. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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:

    1. Open up your workbook
    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.
    Attached Files Attached Files
    Last edited by JBeaucaire; 08-04-2015 at 06:27 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    Please Login or Register  to view this content.


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

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    Quote Originally Posted by newdoverman View Post
    ...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. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

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

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

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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
    Last edited by FlameRetired; 08-02-2015 at 10:08 PM.
    Dave

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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. #18
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count 6 working days (excluding Sunday, Holidays & half days)
    By eve_star1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2015, 08:00 AM
  2. Replies: 0
    Last Post: 10-31-2014, 10:32 AM
  3. Replies: 4
    Last Post: 05-19-2014, 04:28 PM
  4. Count Working days by subtracting bank holidays and weekends
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-08-2014, 07:40 AM
  5. Count Days EXCLUDING ONLY Sundays and a named range for Holidays
    By Seaplane Jack in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-14-2014, 05:03 PM
  6. Employee Time Tracking - Sick days & Consecutive Days
    By notaguru6 in forum Excel General
    Replies: 6
    Last Post: 08-09-2013, 12:50 PM
  7. Replies: 2
    Last Post: 04-08-2005, 03:06 PM

Bookmarks

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