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

jtr8151

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!

Kelly Moore

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

Kelly Moore

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

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

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

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

Kelly Moore

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

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

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

Perfect, thank you!

Kelly Moore

My pleasure to help