Compare two column dates, if over 7 days, list in a third column no

Options

Good afternoon,

I am wanting to take the date of column 1, compare it to the date in column 2 and if the comparison reveals more than 7 days has elapsed to mark column 3 with the word "no". Additionally, if less than 7 days has elapsed to mark column 3 with the word "yes". I've searched these forms and have found similar questions asked but nothing this specific. Any help would be greatly appreciated!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @jtr8151

    Phew, glad it works.

    Try this for your build

    =IF(AND(ISDATE([column 1]@row), ISDATE([column 2]@row)), IF(ABS(NETDAYS([column 1]@row, [column 2]@row)) > 7, "No", "Yes"), "Open")

    Does that work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @jtr8151

    Try this

    =IF(AND(ISDATE([column 1]@row), ISDATE([column 2]@row)), IF(ABS(NETDAYS([column 1]@row, [column 2]@row)) > 7, "No", "Yes"))

    This formula first checks that both columns are dates. It then calculates the absolute difference between the two columns, and responds respective if the result is greater than 7 or not.

    Make sure to edit formula above to replace column names with your column names.

    Will this work for you?

    Kelly

  • jtr8151
    jtr8151 ✭✭
    Options

    Thank you Kelly, I think I am heading in the right direction now! How would I have this formula ignore if both dates aren't listed. For example, if Column 2 didn't have a date yet, I would want the formula to ignore this operation until both had valid dates?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @jtr8151

    It should already be doing that - I added it in. The first IF in the formula is looking that both are dates. The formula should return a blank cell if either/both are blank. Is that not what you are seeing?

    Kelly

  • jtr8151
    jtr8151 ✭✭
    Options

    No if one of the dates is missing, it defaults to yes

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @jtr8151

    Sorry you are having the trouble with the formula. Could you try pasting my original formula into your sheet again (and then updating the cell names) - or posting your current formula. I cannot replicate the Yes when one or both dates are missing. In my sheet the formula shows a blank field when any dates are blank

  • jtr8151
    jtr8151 ✭✭
    Options

    it works, I'm sorry to cause you trouble, it was an error on my end, thank you for the help! To take this formula one step further, if either of the columns do not have a date, I would like the third column to say "Open". Possible?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @jtr8151

    Phew, glad it works.

    Try this for your build

    =IF(AND(ISDATE([column 1]@row), ISDATE([column 2]@row)), IF(ABS(NETDAYS([column 1]@row, [column 2]@row)) > 7, "No", "Yes"), "Open")

    Does that work for you?

    Kelly

  • jtr8151
    jtr8151 ✭✭
    Options

    Perfect, thank you!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    My pleasure to help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!