Using Boolean Operators in Countif with Dates

Hello. I'm not sure why my formula isn't working. I'm guessing the issue is my syntax but I don't see where it is wrong, if that is the issue.


I'm trying to get countif to count the number of rows with a date greater than 1/1/20. The formula does return a value but that value is 0. Here is the formula in case it's hard to see in the screenshot

=COUNTIF({Sofie's Org[TD Trans Date]}, ">" + DATE(2020, 1, 1))


I included a screenshot of the reference column. Clearly there are some dates that are greater than 1/1/20. The reference column is set to date column type.


When I try this in Excel I get a value of 3394 with the analogous formula =COUNTIF(Table1[TD Trans Date],">"&DATE(2020,1,1)).


Any help would be appreciated


Best Answer

  • Edgar Meza Cabrera
    Answer ✓

    Hello. After a bit more research I found the solution.


    It seems using @cell in formula instead of concatenating the ">" to the output of the date function is the way to go (btw what do we call @cell like elements? It wouldn't be formula. Maybe it's calling a function?)


    Anyway, here is the syntax to use based on my initial question.


    =COUNTIF({Sofie's Org[TD Trans Date]}, @cell > DATE(2020, 1, 1))

Answers

  • Edgar Meza Cabrera
    Answer ✓

    Hello. After a bit more research I found the solution.


    It seems using @cell in formula instead of concatenating the ">" to the output of the date function is the way to go (btw what do we call @cell like elements? It wouldn't be formula. Maybe it's calling a function?)


    Anyway, here is the syntax to use based on my initial question.


    =COUNTIF({Sofie's Org[TD Trans Date]}, @cell > DATE(2020, 1, 1))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Edgar Meza Cabrera

    I'm glad you found a solution! Thanks for posting it to help other members.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!