# 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!

• ✭✭✭✭✭✭
Options

Hey @jtr8151

=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

• ✭✭✭✭✭✭
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

• ✭✭
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?

• ✭✭✭✭✭✭
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

• ✭✭
Options

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

• ✭✭✭✭✭✭
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

• ✭✭
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?

• ✭✭✭✭✭✭
Options

Hey @jtr8151

=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

• ✭✭
Options

Perfect, thank you!

• ✭✭✭✭✭✭
Options

My pleasure to help

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!