Comparing dates

Hello everyone,

I have three date columns with individual dates. In a fourth column, I want to display the next upcoming date which is not in the past. In Excel, I would have used something like this:

=MIN(IF(A1:C1>=TODAY(),A1:C1))

I'm not sure how to achieve this in Smartsheet, especially when it comes to comparing columns.

Any help is highly appreciated! Thank you very much.

Find more posts tagged with

Formulas

Accepted answers

All comments

Etienne F.

Hi,

assuming your columns are formatted as dates, the below formula would work

=MIN(COLLECT([Date1]@row:[Date3]@row, [Date1]@row:[Date3]@row, >=TODAY()))

TobiasKarlsruhe

Thank you @Etienne F., that works!

I was aiming for a formula which compares the dates independently from their position in the sheet as I have some columns in-between of the date columns.

I came up with something like this:

=MIN(IF(AND([Date1]@row >= TODAY(), [Date1]@row <> ""), [Date1]@row, ""), IF(AND([Date2]@row >= TODAY(), [Date2]@row <> ""), [Date2]@row, ""), IF(AND([Date3]@row >= TODAY(), [Date3]@row <> ""), [Date3]@row, ""))

Can this be simplified?

Etienne F.

no better idea at this point in time, but please note you can keep first formula if the columns in between are not dates. that would still work even if they're counted in the range of collect!

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.