Countif with Dates

07/22/21
Accepted

Hi all,

I've read notes on other posts with similar dates but haven't been able to figure it out so asking here. Sorry for the repetition. I'm trying to count the number of cells in a column of dates that have a date prior to a date I have stored in another cell. I'm actually doing it with countifs but I can get the other parts to work, just not the date.

The Zero in row 4 of Column 16 is from the below equation. Clearly not accurate

=COUNTIF([Target Hire Date]:[Target Hire Date], "<08/01/2021")


The #UNPARSE error in row 5 of Column 16 is from a different attempt to rather than input the date, reference the date stored in Column 16 row 2.

=COUNTIF([Target Hire Date]1:[Target Hire Date]46,"<"&[Column16]2)

It's really frustrating because I can do this in 2 seconds in excel but it's not working in SmartSheets. In case it matters I do have some blank cells in the column Target Hire Date but when I tried it selecting only the cells with data it still didn't work.


Thanks for any help here!

Jen

Tags:

Best Answer

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    Hi @Jennifer Fortune

    Hope you are fine, please try the following formula:

    =COUNTIF([Target Hire Date]:[Target Hire Date], @cell < Date(2021,08,01))

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • edited 07/22/21

    @Bassam Khalil. When you type @cell, what does that actually mean? Do I type that or do I reference a specific cell there? Thanks! And thank you for the quick reply :)

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    @Jennifer Fortune

    @cell is a function used inside other functions like SUMIFS, COUNTIFS to make it check the criteria in the selected range cell by cell. Please try to use my formula as is it

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • @Bassam Khalil. Got it. Thanks for the explanation. I hadn't realized it was a function in itself and I like to understand so I can apply going forward in new situations. Really appreciate the insights here!!

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    @Jennifer Fortune

    You are welcome and I will be happy to help you any time, please accept my answer and I will appreciate your vote on this post.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Sign In or Register to comment.