Results 1 to 18 of 18

Count Consecutive Days while bridging over days off and holidays

Threaded View

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

    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

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. [SOLVED] How do you count work days excluding weekends and holidays?
    By Hausma in forum Excel General
    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