Two date columns - need to take the earlier date of the two and add 10 years

Daniel Hollywood · 11/02/20

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:

https://community.smartsheet.com/discussion/65854/how-can-i-compare-two-dates-and-then-add-thirty-days-to-whichever-date-is-later

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

Louis Raoul · 11/02/20

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")))

Louis Raoul · 11/02/20

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

Daniel Hollywood · 11/02/20

https://community.smartsheet.com/discussion/comment/267728#Comment_267728

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.

Louis Raoul · 11/02/20

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")))

Daniel Hollywood · 11/03/20

https://community.smartsheet.com/discussion/comment/267730#Comment_267730

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?

Two date columns - need to take the earlier date of the two and add 10 years

Best Answer

Answers

Help Article Resources

Categories