COUNTIF with Dates

Greg Lloyd

I am trying to use COUNTIF referencing two Dates columns. The first "Due Date" column has dependencies. The second "Dates" column is simply a list of all possible due dates from the beginning of the project to the end (with no dependencies). I am trying to create a third column called "Total Due" that counts how many tasks have a given due date. My formula on line one is =COUNTIF([Due Date]$1:[Due Date]$110, [Dates]1). But even though there are Due Dates that match Dates1, the formula returns 0. 

 

Is the formula not working because the "Due Date" column type is Date/Time and the "Dates" column type is Date? Date/Time is not an option for the "Dates" column and I can't change the "Due Date" column type because it has dependencies and was automatically selected.

 

How can I do a count of certain due dates referencing another date cell?

Travis

Hi Greg! You are correct, the issue comes from your Due Date column being a Date/Time column, while your Dates column is a Date column. There are a couple of workarounds you could use.

 

Instead of referencing your Dates column, reference your Due Date column like this:

 

=COUNTIF([Due Date]$1:[Due Date]$110, [Due Date]1)

 

It wouldn’t be your list of all possible dates, but it could tell you how many times the corresponding date exists in the column. 

 

Side note - you can reference the entire Due Date column like this:

 

=COUNTIF([Due Date]:[Due Date], [Due Date]1)

 

The second workaround is to create another Date column and pull in all the dates from your Due Date column using this formula:

 

=[Due Date]1

=[Due Date]2

etc...

 

Next, use this new Date column as the range in your formula.

Kurt Mithoefer

Im trying to create a CountIF statement with a date range to determine how many submissions came in within a given year.  Im using the below formula and it isnt returning the correct value.

 

=COUNTIFS([Date Submitted]:[Date Submitted], ">""2013-12-31", [Date Submitted]:[Date Submitted] "<""2015-1-1")

 

what am I doing wrong?

 

Travis

Hi Kurt,

 

COUNTIFS can only use < > if you are referring to numbers - not dates.

 

The workaround would be to add a Checkbox column with a formula that will check the box if the Date Submitted date is between your range. 

 

Heres the formula to use:

 

=IF(AND([Date Submitted]51 > DATE(2013, 12, 31), [Date Submitted]51 < DATE(2015, 1, 1)), 1)

 

Then run your COUNTIF function off the Checkbox column. 

 

Learning resource: 

http://help.smartsheet.com/customer/en/portal/articles/775363-using-formulas#date

rfhickey

Travis,

You say that " COUNTIFS can only use < > if you are referring to numbers - not dates." 

I have two columns of dates and want to count the number of times that the date in one column is greater (later in time) than the date in the other column in the same row. I have tried using < and > but I keep getting 0, maybe because of what you said above. How then can I count the number of times that the date in one column occurs after the date in another column?

Amanda Durham-Barnes

I wanting to create a total count of listed enteries with a specific date range from another sheet's date column. This would then be used to formulate a percentage of work items completed within the date range.

I am using this Formula

=COUNTIF({NEO Report-New Hire-Internal Transfer-Cert Range 1:{NEO Report-New Hire-Internal Transfer-Cert Range 2}}, "03/10/2019"+"03/11/2019")

But it comes back with "0". I have tried this with just a single date and I get the same thing. 

What am I doing wrong?