+ Reply to Thread
Results 1 to 3 of 3

COUNTIF function not counting dates on 31st

  1. #1
    Registered User
    Join Date
    06-28-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    25

    COUNTIF function not counting dates on 31st

    Hi I am using the formula:
    =COUNTIF($J$12:$J$3081,">="&DATE($T$2,IN1,1))-COUNTIF($J$12:$J$3081,">="&DATE($T$2,IN1,31))
    To count the number of times dates appearing in a certain month appear in the J column. However this is not picking up dates which are on the 31st, I've included >= so not sure why this is happening.
    Last edited by mattmorris; 07-03-2012 at 10:08 AM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTIF function not counting dates on 31st

    The way the formula works is that the first COUNTIF counts all dates on or after the 1st of the month (including dates beyond the end of the month)......the second COUNTIF then subtracts all those dates that are beyond the end of the month (so that the final result is only dates within the correct month), so you don't want >=, you want >.

    ....but obviously some months don't have 31 days so that won't work correctly in February, for instance, unless you change the 31 each time, better to use this version with the 1st of the next month (where you do still want >=), so that the same formula will work for any month

    =COUNTIF($J$12:$J$3081,">="&DATE($T$2,IN1,1))-COUNTIF($J$12:$J$3081,">="&DATE($T$2,IN1+1,1))

    or in Excel 2010 you can do that more simply with COUNTIFS, i.e.

    =COUNTIFS($J$12:$J$3081,">="&DATE($T$2,IN1,1),$J$12:$J$3081,"<"&DATE($T$2,IN1+1,1))
    Last edited by daddylonglegs; 07-03-2012 at 10:34 AM.
    Audere est facere

  3. #3
    Registered User
    Join Date
    06-28-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: COUNTIF function not counting dates on 31st

    Thanks alot! This worked perfectly

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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