Forum Discussion

markyehl's avatar
markyehl
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...
excel
Formulas and Functions
markyehl's avatar
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's avatar
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's avatar
    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?

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 17, 2020

      PeterBartholomew1 , I don't think any 'officially approved' strategy exists. With SIGN that's a good finding, but from my point of view is not practical for such purposes. If collect variants you forgot

      = SUM(Boolean#+0)
= SUM(Boolean#*1)
= SUM(Boolean#*Boolean#)
= -SUM(-Boolean#)

      Perhaps something else, that's Excel

      • mtarler's avatar
        mtarler
        Silver Contributor
        Jul 17, 2020

        SergeiBaklan  basically your examples here are that any math action will prompt Excel to try to convert the values, in this case Boolean, to a number.  The very popular "--" forces a negate and then negate again so you end up with the "original value" as a value instead of text or Boolean. I suspect its popularity is that it is probably the most compact way to force a conversion and result in the "same" value and is visually unobtrusive.  I'm sure you two already know all this but thought I would add it for others that might read this thread.   

Resources