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.
MM/YYYY is not a valid Date value format in AppSheet.
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?
Not when used with a column of type Date. Youโll either need to make the column type Text, or use a full Date.
User | Count |
---|---|
35 | |
35 | |
27 | |
23 | |
18 |