Determining If A Formula Date Is In The Past

I have a formula (Join(Collect)) that finds a date in a range, and displays it when conditions are met.

I have a second formula that is trying to compare the first "formula date" to determine if the date is in the past.

My issue is that the first date is apparently not a real date data type, and I am having an issue in converting the formula date into an actual date for use in comparison to today.

Any thoughts?

Find more posts tagged with

Accepted answers

All comments

Darren Mullen

@Brian McElligott Any screenshots you can share? :)

Paul Newcome

The issue is that te JOIN function outputs a text string. It is no longer stored as a date on the back-end. Try replacing it with an INDEX.

=JOIN(COLLECT(………….))

changes to

=INDEX(COLLECT(………….), 1)

You may need to wrap it in an IFERROR for instances where there is no matching row in the source data.

Brian McElligott

This is the formula that looks over a range of columns and, if any have a date, pulls that date:

=(IF(ISBLANK(JOIN(COLLECT([Alternate Cypress Creek Campus Event Date]@row :[Alternate South Austin Campus Event Date]@row , [Alternate Cypress Creek Campus Event Date]@row :[Alternate South Austin Campus Event Date]@row , ISDATE(@cell )), CHAR(10))), [Requested Event Date]@row , (JOIN(COLLECT([Alternate Cypress Creek Campus Event Date]@row :[Alternate South Austin Campus Event Date]@row , [Alternate Cypress Creek Campus Event Date]@row :[Alternate South Austin Campus Event Date]@row , ISDATE(@cell )), CHAR(10)))))

After changing the Join collect to an Index collect:

=(IF(ISBLANK(INDEX(COLLECT([Alternate Cypress Creek Campus Event Date]@row :[Alternate South Austin Campus Event Date]@row , [Alternate Cypress Creek Campus Event Date]@row :[Alternate South Austin Campus Event Date]@row , ISDATE(@cell ), 1), CHAR(10))), [Requested Event Date]@row , (INDEX(COLLECT([Alternate Cypress Creek Campus Event Date]@row :[Alternate South Austin Campus Event Date]@row , [Alternate Cypress Creek Campus Event Date]@row :[Alternate South Austin Campus Event Date]@row , ISDATE(@cell ), 1), CHAR(10)))))

I am getting the following error:

#INCORRECT ARGUMENT

Paul Newcome

You will need to adjust things to also remove your CHAR(10) delimiters throughout.

Try this:

=IF(IFERROR(INDEX(COLLECT([Alternate Cypress Creek Campus Event Date]@row:[Alternate South Austin Campus Event Date]@row, [Alternate Cypress Creek Campus Event Date]@row:[Alternate South Austin Campus Event Date]@row, ISDATE(@cell), 1), "") = "", [Requested Event Date]@row, INDEX(COLLECT([Alternate Cypress Creek Campus Event Date]@row:[Alternate South Austin Campus Event Date]@row, [Alternate Cypress Creek Campus Event Date]@row:[Alternate South Austin Campus Event Date]@row, ISDATE(@cell), 1))

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.