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

Options
edited 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:

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

• ✭✭✭✭
Options

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

• Options

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.

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!