COUNTIF with dates

[markyehl]

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 =COUNTIF(E3:E55,">=D59") and it returns a value of zero.  Can I do this with COUNTIF, or is there another function I should use?

Also, can I use the TODAY function in the COUNTIF function instead referring to the other cell?  What would that look like?  Thanks for your help.

[#2]

@markyehl 

That's like

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

[#3]

@markyehl 

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)

[#4]

@Peter Bartholomew 

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.

[#5]

Hello, @markyehl 

My understanding of challenge posted here is as follows,

1. Student check-in dates are either Past or Today's  date.
2. Needs to calculate how many total Students are active check in till date.

If my understanding is correct, then formula should be as below, included today function in formula. Also please find attached sample sheet.

Hope this will of help to you.

[#6]

@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.

[#7]

@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.

[#8]

@Sergei Baklan 

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?

[#9]

@Peter Bartholomew , 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

[#10]

@Sergei Baklan 

Perhaps something else, that's Excel

---

True

[#11]

@Sergei Baklan  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.   

[#12]

@mtarler 

Yes, double dash is most popular as the fastest way from typing point of view. And at the same time is most confusing one, Since quite many people don't know about the difference between negation and subtraction, Excel designers didn't find separate character for the negation in ANSII table. Thus people are surprised why =-Booleans# works, but =+Booleans# not; or why =-6^2 returns plus 36, not minus 36.

[#13]

@mtarler 

You are right; I probably should have referred to the more positive features of '--' rather than limiting my comment to the more negative 'mathematically obscure'.  I do remember being disoriented by it, though, when I first encountered the notation in a SUMPRODUCT, but one gets used to it.

[#14]

@Peter Bartholomew 

Some ago (long ago) I shifted in SUMPRODUCT() on *1 since had couple of cases when people removed double dash considering minus on minus in any case returns plus, and two minuses only complicate the formula, But now I'm again mostly on double dash.