+ Reply to Thread
Results 1 to 4 of 4

Count days falling within given period (with criteria)(absence tracker)

  1. #1
    Forum Contributor
    Join Date
    02-07-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    105

    Count days falling within given period (with criteria)(absence tracker)

    Hi everyone,

    Searched this forum and couldn't find a solution.
    I am trying to create an absence tracker.

    Have several types of absence.
    Have multiple periods.
    Have multiple employees.

    What formula would you suggest here.

    I have:
    Please Login or Register  to view this content.
    What I am trying to achieve is:
    criteria:
    1. Search absence period: dynamic (drop down)
    2. Search absence type: dynamic (drop down)
    3. Search employee: dynamic (drop down)


    records of absence:
    always between dates
    outcome:
    count number of days of absence
    if absence starts on or after given month start date, and
    if absence ends on or before given month end date

    if absence belongs to employee X
    if absence is of type X
    further outcome:
    if there are absences which started before given month and ended before month end or after month end
    then count them as well.

    workbook attached.
    Hope you can guide me thank youCapture1.PNG
    Attached Files Attached Files
    Last edited by annazet; 04-11-2020 at 05:03 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,705

    Re: Count days falling within given period (with criteria)(absence tracker)

    The formula is not so short, but seems it works. Unfortunately countif is useless here, as it counts occurences while you need not number but total length of such events.
    It takes into account absence which started before first date, but continued into (or even after) current month, as well as started current month and finished after eomonth.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you limit only to absences started and finished within the month boundaries, it will be much shorter:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Instead of sumproduct regular sum could be used, but then the formula needs to be array committed, so confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you could get an error or a clearly incorrect answer.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,853

    Re: Count days falling within given period (with criteria)(absence tracker)

    Please try
    =SUM(TEXT(IF(H3:H9>EOMONTH(C2,0),EOMONTH(C2,0),H3:H9)-IF(G3:G9<C2,C2,G3:G9)+1,"0;\0;0")*(F3:F9=C4)*(I3:I9=C3))
    Ctrl+Shift+Enter

    or
    =SUMPRODUCT(TEXT(1+TEXT(H3:H9,"[>="&EOMONTH(C2,0)&"]"&SUBSTITUTE(EOMONTH(C2,0),0,"\0")&";0")-TEXT(G3:G9,"[<"&C2&"]"&SUBSTITUTE(C2,0,"\0")&";0"),"0;\0;0")*(F3:F9=C4)*(I3:I9=C3))

    Normal enter
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-07-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    105

    Re: Count days falling within given period (with criteria)(absence tracker)

    Thank you both. Both solutions do what I required. So much appreciated!

+ 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. staff absence tracker inc rolling year
    By BIDD in forum Excel General
    Replies: 4
    Last Post: 09-16-2019, 02:58 PM
  2. [SOLVED] How to Exclude Weekends in Employee Absence Tracker Count
    By pugz in forum Excel General
    Replies: 20
    Last Post: 07-30-2018, 03:09 AM
  3. Replies: 11
    Last Post: 11-02-2016, 09:52 AM
  4. Absence Tracker Template by employee
    By sabrinaxiomara in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-07-2015, 09:44 PM
  5. Absence tracker problem
    By pandabear28 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2014, 05:23 AM
  6. Absence Tracker will not calculate
    By Vmgballer1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-13-2013, 08:48 AM
  7. [SOLVED] Count days for specific year falling within in multi-year date range
    By jslo2013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2012, 05:58 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