Countif with Dates
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
Best Answer
-
Hope you are fine, please try the following formula:
=COUNTIF([Target Hire Date]:[Target Hire Date], @cell < Date(2021,08,01))
☑️ 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"
Answers
-
Hope you are fine, please try the following formula:
=COUNTIF([Target Hire Date]:[Target Hire Date], @cell < Date(2021,08,01))
☑️ 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. 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 :)
-
@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
☑️ 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!!
-
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.
☑️ 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"
Help Article Resources
Categories
Check out the Formula Handbook template!
