Treating Formatting Date Field as Value

[V-A]

Using the AppSheet Journal template, I created a virtual column to format the date of each row (in "YYYY-MMM-DD" format) and used that virtual column as the "Group by" column.  Since the app treats this virtual column as text, the months are sorted alphabetically, e.g. Feb is displayed before Jan.

It seems the only way around this is to have that text virtual column treated as a value.  Is this possible while still having that virtual column displayed formatted?

[1minManager]

Use this in virtual column

https://support.google.com/appsheet/answer/10107922?hl=en

Then group by this column.  Though you might want to consider whether you also need to group by Year() as well

Simon, 1minManager.com

Appsheet developer for hire and reward ‌😉‌

[V-A]

Thanks.  Grouping by month has the user filter by month first and then the formatted date rows are displayed.  I also tried sorting by month and date.

Is there a way to have all rows displayed by default rather than filtering first?

[1minManager]

Any grouping columns are hidden unless you order by a clicking the top of the columnm then they show up

[V-A · Accepted solution]

Thanks for clarifying.  The two-step filter won't work for my use case, though the single column grouping worked without user pre-filtering by month or year.

This seems to be a known AppSheet limitation.  The closest I can get to my desired output format is by using the "Use long date format" under the date type.  This removes the need for a virtual column.  Otherwise, keep the virtual column and for grouping have the date text displayed in YY-MM-DD format (rather than DD-MM-YY common in some locales outside the U.S.) and numerically ("MM" rather than "MMM" or "MMMM").

The "long date format" is likely a system setting.  I don't know if and how to modify it.  If you do, please let me know.

[Steve]

Formatting of Date, Time, and DateTime values is controlled by the device's locale settings. The app itself has no control over that, nor should it. If you "must" control the display format, you will need to generate a Text column value containing the temporal value in the desired format.

[V-A]

Thanks for the further insight into how AppSheet integrates with the device's locale settings.

The problem with setting a numeric, chronological field, such as a date column, as text strings surfaces when grouping by that column since the app orders it alphabetically, e.g. February precedes January when grouped in ascending order.

I was hoping AppSheet would work like Google Sheets or MS Excel and maintain the underlying date as a serial number and permit custom formatting for the displayed column.  Note that I also changed the source Google Sheets worksheet to display the column in the desired custom number format.  I suppose this isn't how databases work.

[Steve]

Consider this:

Solved: Re: Sort Month Name - Google Cloud Community

[V-A]

Thanks.  This was helpful.  It's drawback is that it only applies to cases where just the calendar month is used for sorting.  The user applied an INDEX(LIST()) expression, as demonstrated in the docs, which returns a text value.

In my use case, I'm using a non-standard format, "YYYY-MMM-DD  (DDD)", e.g "2023-Jan-02  (Mon)" where the month's abbreviation is in the middle of the displayed date.

I'm still trying to figure out a way to have AppSheet sort months chronologically by their names or abbreviations (without numerical prefixes, such as "12-Dec").