+ Reply to Thread
Results 1 to 12 of 12

Need my attendance tracker to count consecutive days as one event instead of individually

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Need my attendance tracker to count consecutive days as one event instead of individually

    Our policy is, "if you are absent consecutive days, it’s considered one (1) incident."

    My tracker works for everything else, except it treats each absent as an incident. How can I make it only count 1 of consecutive days.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    Try

    in D6

    =SUMPRODUCT(--((FREQUENCY(IF(D$4=$I6:$NI6,IF($I$5:$NI$5>=EDATE($G$1,-6),IF($I$5:$NI$5<=$G$1,COLUMN($I6:$NI6)))),IF(D$4<>$I6:$NI6,IF($I$5:$NI$5>=EDATE($G$1,-6),IF($I$5:$NI$5<=$G$1,COLUMN($I6:$NI6)))))>0)))

    Enter with Ctrl+Shift+Enter

    copy down

  3. #3
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    I get a #NUM!

    First it said it found a typo and corrected it.

    wait*

    Why D6?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    See attached:

    Look at Name2 : 27 Sept onwards
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    D6 because I tested on UAs

    If this applies to all absent types, drag across.

    You will GP numbers go from 2 to 1 for Name1 and Name2

    Signing off for today (tonight!).
    Last edited by JohnTopley; 11-01-2016 at 06:05 PM.

  6. #6
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    dang sorry, i was looking at my modified form, i inserted three rows and now D6 is D9 so I forgot.

    Either way, I put in a test name and put in your formula in "D" next to it and got that error. Its because of the row changes. My bad. let me adjust it and then test it! Thanks


    edit**

    OK, i'm close but not there yet. Its like it will not count anything more than one absence, regardless of when
    Attached Files Attached Files
    Last edited by taylorsm; 11-01-2016 at 06:27 PM.

  7. #7
    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: Need my attendance tracker to count consecutive days as one event instead of individua

    Did you try JohnTopley's formula in the file post #4? It has to be array entered.

    It works at my end.
    Dave

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    1) Paste the following variation of John's array entered formula (simply adjusted to match the new setup) into D9 of the file attached to post #6:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    2) Press Ctrl, Shift and Enter simultaneously,
    3) Drag the fill handle down to D15.
    Let us know if you have any questions.
    Last edited by JeteMc; 11-01-2016 at 08:33 PM.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    Your formula was wrong: use the corrected one provide by JeteMc (mine adjusted for row changes).

  10. #10
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    Thank you!

    Would something like this work?
    Please Login or Register  to view this content.
    What are the differences? I just recognize SUMPRODUCT vs FREQUENCY

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    I see you have marked your DUPLICATE (!) post as solved so please do the same here.

  12. #12
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Need my attendance tracker to count consecutive days as one event instead of individua

    Yeah you referring to the duplicate that had was up for 10 hours before a response, while this one, posted four hours later had a response in 30 minutes. Gotcha. And as you can tell, i mark solved when solved. I was hoping to understand the difference in approaches before marking as solved and because I wasn't 100% if the other formula was correct, but since it was a duplicate i marked it solved because there isn't a way to delete, or at least when asked, no one has been able to tell me.

    Thanks

+ 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] Adjust attendance tracker to treat consecutive absences as a single.
    By taylorsm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2016, 09:11 AM
  2. Replies: 1
    Last Post: 03-09-2016, 01:05 PM
  3. Replies: 17
    Last Post: 08-03-2015, 06:15 AM
  4. [SOLVED] Count last 7 days of pupil attendance that exceeds 31
    By wilson23 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-30-2015, 10:11 AM
  5. Replies: 1
    Last Post: 10-24-2014, 09:57 PM
  6. Formula to look back 90 days and drop point on attendance tracker
    By tmorr24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-22-2014, 01:27 AM
  7. [SOLVED] Listing consecutive days without inputting each one individually
    By jcrouse in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-08-2005, 11:06 AM

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