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:

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!

Find more posts tagged with

Accepted answers

Louis Raoul

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

All comments

Louis Raoul

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

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

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

Daniel Hollywood

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.