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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!