+ Reply to Thread
Results 1 to 9 of 9

DATE formula nested in SUMIFS not picking up last day of month

  1. #1
    Registered User
    Join Date
    10-11-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    58

    DATE formula nested in SUMIFS not picking up last day of month

    Hi,

    Happy New Year

    On my attached attendance tracker there is a report sheet called 'All Employees' which is designed to display how many days someone has taken per month, based on a selection from the attendance parameters drop-down menu.
    The basic concept of this page works, e.g employees' names list correctly and i can select the appropriate attendance parameter and get a result.

    However the issue occurs when some has a "attendance date" that is either the last day of the month or includes the last day of a month. (leave tracker sheet is used to record any leave)
    this renders my expected result to return nothing and leaves the monthly count unaffected.

    on the leave tracker there is wto employees both called Adam opn the leaver tracker with examples of leave that have the issue.
    1 is for the last day 31/01/2018 and the other is 29/05/2018 to 01/06/2018

    on the all employee reports page i should see the following
    Adam B should have a count of 1 in the Jan cell
    Adam P should have a count of 3 for May and 1 for Jun

    Selecting different attendance parameters doesn't affect the outcome, which leads me to believe my SUMIFS formula needs changing from a DATE to an EOMONTH but i can't get my head round how it should look for some reason

    If anyone can help or shed some light it would be great

    Thanks
    Mark
    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,005

    Re: DATE formula nested in SUMIFS not picking up last day of month

    Try

    =IF(SUMIFS(tbl_Leave[Days],tbl_Leave[Name],$D12,tbl_Leave[Start Date],">="&DATE(Calendar_Year,1,1),tbl_Leave[End Date],"<="&DATE(Calendar_Year,1,31),tbl_Leave[Leave],$S$7)<>0,SUMIFS(tbl_Leave[Days],tbl_Leave[Name],$D12,tbl_Leave[Start Date],">="&DATE(Calendar_Year,1,1),tbl_Leave[End Date],"<="&DATE(Calendar_Year,1,31),tbl_Leave[Leave],$S$7),"-")

  3. #3
    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,005

    Re: DATE formula nested in SUMIFS not picking up last day of month

    It does check for holidays which go over a month end as per Adam P.

  4. #4
    Registered User
    Join Date
    10-11-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    58

    Re: DATE formula nested in SUMIFS not picking up last day of month

    Hi John,

    Ive made the correction you suggested and now i get holidays for a complete month to work so that great. Thank You

    Still having the issue when a holiday rolls over from one month to the next.
    So i am having to work around this by putting in 2 holiday lines in the leave tracker for example
    29/01/2018 to 31/01/2018 (3 days)
    01/02/2018 to 02/02/2018 (2 days)

  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,005

    Re: DATE formula nested in SUMIFS not picking up last day of month

    Using your current formula you could make it more "generic":

    =IF(SUMIFS(tbl_Leave[Days],tbl_Leave[Name],$D11,tbl_Leave[Start Date],">="&DATE(Calendar_Year,MONTH(S$9&0),1),tbl_Leave[End Date],"<="&EOMONTH(DATE(Calendar_Year,MONTH(S$9&0),1),0),tbl_Leave[Leave],$S$7)<>0,SUMIFS(tbl_Leave[Days],tbl_Leave[Name],$D11,tbl_Leave[Start Date],">="&DATE(Calendar_Year,MONTH(S$9&0),1),tbl_Leave[End Date],"<="&EOMONTH(DATE(Calendar_Year,MONTH(S$9&0),1),0),tbl_Leave[Leave],$S$7),"-")

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: DATE formula nested in SUMIFS not picking up last day of month

    Just an FYI for performance improvement in hiding 0's..

    Doing this basically repeats the actual calculation twice when it's not 0.
    =IF(SUMIFS(blah blah)<>0,SUMIFS(blah blah),"-")
    Repeating the same calculation twice.

    Try this instead
    =IFERROR(1/(1/SUMIFS(blah blah)),"-")

    This way you only do the actual calculation once.

  7. #7
    Registered User
    Join Date
    10-11-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    58

    Re: DATE formula nested in SUMIFS not picking up last day of month

    Jonmo,

    Thats a sneaky little trick but seems to work pretty well.
    Could you explain a bit more on the idea behind it please?

  8. #8
    Registered User
    Join Date
    10-11-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    58

    Re: DATE formula nested in SUMIFS not picking up last day of month

    John,

    the Last forumla you posted still has the same result so i will instruct the HR department to split the holidays if they cross over multiple months.

    Thanks for all the help again

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: DATE formula nested in SUMIFS not picking up last day of month

    Quote Originally Posted by MarkPr View Post
    Thats a sneaky little trick but seems to work pretty well.
    Could you explain a bit more on the idea behind it please?
    It's really just very simple math.
    Take ANY number (the result of your sumifs)
    Divide 1 by that number.
    Then divide 1 by that result.
    This will return the same original number
    Unless the original number is 0 (or it's not a number at all)
    In that case, it returns an error, #Div/0 if the original number was 0, or #Value! if it was a non numeric value.
    Then it's handled by IFERROR.

    X = Any Number
    Z = 1 divided by X
    1 divided by Z = X
    Unless X is 0 or non numeric.

    Hope that helps.
    Last edited by Jonmo1; 01-04-2018 at 05:58 PM.

+ 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] Picking up wrong data using SUMIFS
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-02-2018, 07:44 AM
  2. Week to date, month to date, year to date using SUMIFS
    By jaredf in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-23-2017, 01:51 PM
  3. [SOLVED] SumIfs that cannot sum the Date to its month range
    By JESSHOR60 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-06-2015, 07:18 PM
  4. [SOLVED] Using SUMIFS to count the number of inquiries per month based on the date of inquiry
    By Tryin2Excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-25-2014, 04:11 AM
  5. [SOLVED] SUMIFS and Date Criteria (Month Year etc...) but without helper column?
    By JungleJme in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-16-2013, 07:21 AM
  6. [SOLVED] Use TEXT function as Criteria on Sumifs to convert month/year to date format
    By chico.corrales in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2012, 07:49 PM
  7. SumIFS() filtered by the month of a date cell
    By truecrisis in forum Excel General
    Replies: 2
    Last Post: 07-06-2010, 07:57 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