Date grouping - invalid date - due to formula in spreadsheet

[Stephen_Walker]

I am trying to group by date (in the MM/YYYY format). However, the grouping shows invalid date.

II think that the reason for the error is because the Excel sheet uses a formula to pull the date. Below is the Excel formula - Note - this is an array formula.
{=TEXT(MIN(IF(‘System Components’!$A$2:$A$2000=‘Client Data’!A2,IF(‘System Components’!$D$2:$D$2000<>0,‘System Components’!$D$2:$D$2000))),“mm/yyyy”)}

If I copy the cells in the Excel and paste them as ‘values’, then the grouping works as it should. But when the cells contain the formula, then the grouping does not work.

In Appsheet, for the date column in question, the ‘spreadsheet formula’ that Appsheet has generated for me is: =ArrayFormula(MIN(IF(‘System Components’!R2C1:R2000C1=‘Client Data’!RC[-8],IF(‘System Components’!R2C4:R2000C4<>0,‘System Components’!R2C4:R2000C4))))

Can someone please help me solve this - I’d like to keep the formula in the excel cells and enable grouping on this column by date (Ascending).

I have tried to create an additional column and reference the column with the array formula, but this still shows invalid date.

[Steve]

MM/YYYY is not a valid Date value format in AppSheet.

[Stephen_Walker]

Thanks Steve,

I am wanting to display Month & Year - are there any valid date formats for this that can be grouped?

Even if I remove the ‘TEXT’ conversion, and leave the value as the standard Excel date format, it still does not work…

Perhaps a solution is to do the “MIN IF” formula in Appsheet rather than in Excel?

[Steve]

Stephen_Walker:

I am wanting to display Month & Year - are there any valid date formats for this that can be grouped?

Not when used with a column of type Date. You’ll either need to make the column type Text, or use a full Date.