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

Options

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?

• ✭✭✭✭✭✭
Options

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%)

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭
Options

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

• Options

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.

• edited 06/23/22
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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%)

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

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

Thanks,

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!