Two date columns - need to take the earlier date of the two and add 10 years
Hello!
I am hoping for some assistance with nailing down this formula so that it displays 10-years on top of the earlier date from two columns. I found a prior community post with the below formula, but I just can't seem to get it to work the way I want it to.
=IF(AND(ISDATE([Last AF]@row), ISDATE([Last BIR]@row)), MIN([Last AF]@row, [Last BIR]@row) + 3650))
Reference Post:
*Last AF and Last BIR can either both contain dates, or one may be empty.
**If both columns do not contain dates, can the formula column state "No Data".
Any help is appreciated.
Thank you!
Best Answer
-
correct,
here is a more comprehensive one that seems to work well, but I'm sure there is a way to make it a bit more compact
=IF(AND([Last AF]@row = "", [Last BIR]@row = ""), "No Data", IF(OR(ISDATE([Last AF]@row), ISDATE([Last BIR]@row)), MIN([Last AF]@row, [Last BIR]@row) + 3650, IF(AND(ISDATE([Last AF]@row), ISDATE([Last BIR]@row)), MIN([Last AF]@row, [Last BIR]@row) + 3650, "No Data")))
Answers
-
Hi Daniel,
=IF(AND(ISDATE([Last AF]@row), ISDATE([Last BIR]@row)), MIN([Last AF]@row, [Last BIR]@row) + 3650, "No Data")
This one will show no data if either or Last xxx are empty
Raoul
-
That doesn't seem to work, it's only returning the 10yr date if the Last BIR column has a date filled in. If only the Last AF column has a date, it produces "No Data". Perhaps I explained it incorrectly. It should only show "No Data" if neither column has a date inputted.
-
correct,
here is a more comprehensive one that seems to work well, but I'm sure there is a way to make it a bit more compact
=IF(AND([Last AF]@row = "", [Last BIR]@row = ""), "No Data", IF(OR(ISDATE([Last AF]@row), ISDATE([Last BIR]@row)), MIN([Last AF]@row, [Last BIR]@row) + 3650, IF(AND(ISDATE([Last AF]@row), ISDATE([Last BIR]@row)), MIN([Last AF]@row, [Last BIR]@row) + 3650, "No Data")))
-
This did it, thank you!
Another thought I had on this would be to add another column as a "counter" to determine where a specific unit is in the process. For example, if the [Last XXX] date is 1/1/15 and it's 1/1/20, the counter column would display 5 since we're in the 5th year. Would this be possibly a similar formula where it takes the latest date between the two columns, and spits out the respective year in cycle?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!