Count date in one cell is greater than date in another cell

Scott Arnold

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. How can I count the number of times that the date in one column occurs after the date in another column?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/334103#Comment_334103

Can you do a hidden helper column on the source sheet? Much easier to do part of this locally rather than remotely.

In the helper column:

=IF([Date Column A]@row > [Date Column B]@row, 1)

This puts a 1 in the helper column for any rows where Date column A is greater than Date Column B.

On the remote sheet:

=COUNTIF({Remote Helper Column}, 1) / COUNTIF({Remote Date Column A}, ISDATE(@cell))

The first formula counts the number of rows with a 1 in the helper column, divided by the second formula which counts the number of rows with a date value in Date Column A.

This will give you a decimal of the positive rate. Format this column for % and it will convert to a percent value automatically (ex .67 = 67%)

John Jonassen

=countifs({Date Column},>=[Your opening Date Cell],{Date Column},<=[Your closing Date Cell])

Take out the equals if you don't want to include the dates in the two cells in your counts.

Scott Arnold

I probably should be more specific. The 2 columns are on a separate sheet. What I am trying to do is determine the success rate of the Complete Date hitting or beating the Target Date.

Dylan Eggers

Does anyone have a suggestion here? I am trying to accomplish the same as OP.

Paul Newcome

You would need to insert a hidden helper column that flags rows that you want to count. Then run your COUNTIFS on this hidden helper column being flagged.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/334103#Comment_334103

Can you do a hidden helper column on the source sheet? Much easier to do part of this locally rather than remotely.

In the helper column:

=IF([Date Column A]@row > [Date Column B]@row, 1)

This puts a 1 in the helper column for any rows where Date column A is greater than Date Column B.

On the remote sheet:

=COUNTIF({Remote Helper Column}, 1) / COUNTIF({Remote Date Column A}, ISDATE(@cell))

The first formula counts the number of rows with a 1 in the helper column, divided by the second formula which counts the number of rows with a date value in Date Column A.

This will give you a decimal of the positive rate. Format this column for % and it will convert to a percent value automatically (ex .67 = 67%)

Dylan Eggers

I was able to figure this out, but yes this was the correct solution!

Thanks,