Forum Discussion

Copper Contributor

Jul 16, 2020

COUNTIF with dates

We have students checking in various dates. I have a column of check-in dates and would like to know how many have checked in as of today. Set a cell (D59) for today's date (=TODAY()) and used =COU...

Formulas and Functions

PeterBartholomew1

Silver Contributor

Jul 16, 2020

The way you have coded the formula

=COUNTIF(E3:E55,">=D59")

makes the D59 a literal text string and not a cell reference. Try

=COUNTIF(E3:E55,">="&D59)

markyehl
Copper Contributor
Jul 16, 2020
PeterBartholomew1
Peter and Sergei - thank you so much, it worked.
Is there a way to nest the TODAY function in the COUNTIF argument and not reference cell D59? Just curious.
- SergeiBaklan
  Diamond Contributor
  Jul 16, 2020
  markyehl
  
  And if you are on Excel 365 it could be
  
  =SUM(--(E3:E55>=TODAY()) )
  
  Double dash is needed to convert logical TRUE or FALSE into 1 or 0.
  - PeterBartholomew1
    Silver Contributor
    Jul 17, 2020
    SergeiBaklan
    At various times, I have used
    
    = SUM(SIGN(Boolean#)) 'The help page doesn't even mention Booleans = SUM(+N(Boolean#)) 'Documented but does not accept arrays = SUM(--Boolean#) 'Mathematically obscure syntax that would appear to be a null operator
    
    and have still to commit to one. Is there an 'officially approved' strategy?
- PeterBartholomew1
  Silver Contributor
  Jul 16, 2020
  markyehl
  Yes, you can nest TODAY, or other functions, to build a COUNTIF criterion
  =COUNTIF(E3:E55,">="&TODAY())
  It is also possible to 'hard-wire' the number as text within the criterion string
  =COUNTIF(E3:E55,">=44028")
  =COUNTIF(E3:E55,">=16/07/2020")
  What (sadly) is not allowed is to use a function within the criterion range, e.g.
  =COUNTIF(MONTH(E3:E55),">6")
  because that converts a range reference into an array.

Resources