Determining If A Formula Date Is In The Past

Brian McElligott

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?

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