Compare two column dates, if over 7 days, list in a third column no
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

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

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

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?

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

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

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

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?

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

Perfect, thank you!

My pleasure to help
Help Article Resources
Categories
Check out the Formula Handbook template!