Format a Virtual Column that is a List of Dates

[Markus_Malessa]

So I know that for Date Columns I can format such as 10/31/2020 to Oct 2020 for example by using TEXT([Date Column], “MMM YYYY”). However I am wondering how would this be accomplished if I had a Virtual Column that is made up of a LIST of Dates? Using the same principal TEXT(List([Date Column]), “MMM YYYY”) results in an Error “TEXT function with two arguments requires a temporal type and a format string”.

Anyone have any ideas?

[LeventK]

You cannot apply that to a List type unfortunately.

[Suvrutt_Gurjar]

Markus_Malessa:

Anyone have any ideas?

As rightly guided by @LeventK, the TEXT() cannot be applied to a list.

There could be a possible workaround with another column being added. If you are okay with that, you may wish to consider the following workaround approach. Basically, the approach involves creating another date column at source level with the desired format and then making a list out of it.

Representing a Date Range List as a List of Days Questions

Hi. I have a column called [Date Range List] which has an AppFormula that returns a list of Dates based on the [From] and [To] Date values. What I want to achieve is to add an additional field called [Day Range List] and this would be a Day of the Week representation of what is in the [Date Range List] field. e.g. 30/11/2020,01/12/2020,02/12/2020 would be Monday, Tuesday,Wednesday. Any help would be great, thank you.

[Markus_Malessa]

Thank you for the input @Suvrutt_Gurjar and @LeventK! What I ended up doing is introduced a Virtual Column to the table that stores my dates with the Text() formatter there, and then just selecting that column.

I had to do quiet the workaround for the purpose I needed this for because I actually had to generate two separate date lists, then subtract the lists from each other to see which dates were missing in one of the lists and then formatting the dates. It required the addition of a new table, something that could have been avoided if Appsheet had a FOR LOOP function, but I think I made it work in the end.

[Suvrutt_Gurjar]

Oh okay, thank you for sharing your requirement and solution @Markus_Malessa.
Yes, it is evident that your requirement was a bit complex with two sets of lists requiring subtraction, etc. Interesting to know that you solved it by adding a new table.

[Bellave_Jayaram]

@Markus_Malessa, would you mind sharing your use case and solution here? It might help others in future. Thanks.

[Markus_Malessa]

Sure. So I needed two date lists, one that was the 28th of every month and the other end of week for every week. So I have a Google spreadsheet that already has some tabs I use to manage other aspects of this application, so I added two more tabs to represent the two date lists ranging from 2000 - 2100 (no real reason here other than I figured that would set me up for the next 80 years or so).

I added both these tables to my application, and added a virtual column to each table to format the dates how I needed them, in the monthly table MMM YYYY and the weekly table it was just the date.

Then I added a virtual column in my TWUA table that would pull a date range from either the monthly or weekly table based on the [ReportingFrequency] column and would subtract a list of dates contained in the [Related WaterUseDatas] virtual column, resulting in a list of dates that were essentially missing from the [Related WaterUseDatas]. At this time I unfortunately had to add another virtual column that took the list of the reduced date list and just selected the formatted date column.

In the end my virtual column for the reduced date list has an expression like so:
IF([ReportingFrequency] = "Monthly",
SELECT(Mthly_Report_Dates[Date],
AND(
[Date] > EOMONTH([DateOfOrder], -1),
[Date] <= [ExpirationDate],
[Date] <= IF(DAY(TODAY()) > 10, EOMONTH(TODAY(), -1), EOMONTH(TODAY(), -2))
)
) - SORT(UNIQUE([Related WaterUseDatas][Date]), FALSE),
SELECT(Wkly_Report_Dates[Date],
AND(
[Date] > EOMONTH([DateOfOrder], -1),
[Date] <= [ExpirationDate],
[Date] < TODAY()
)
) - SORT(UNIQUE([Related WaterUseDatas][Date]), FALSE)
)

And the other virtual column that pulls the formatted date expression is:
IF([ReportingFrequency] = "Monthly", SELECT(Mthly_Report_Dates[Formatted Date], IN([Date], [Missing Reports])), SELECT(Wkly_Report_Dates[Formatted Date], IN([Date], [Missing Reports])))

Turned out quiet complicated but I needed to identify for each TWUA what the missing reports are whether that is for a TWUA that reports monthly or weekly. In a JS programming environment I would have used a FOR LOOP to generate my list of dates that should be there, rather than introducing the two date tables.