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

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?

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!