Compare a list of date with today

[Tjfyuuii]

Hi,

In my table called "Vehicles" I created a virtual column which should allow me to know if today's date is present in a list of dates. My formula is as follows:

IF(IN(TODAY(),(SELECT(Pret[V_PRET_LISTJOURS],[Pret_VehicleId]=[_THISROW].[Vehicle_Id]))),
"Borrowed",
"Available")

The [V_PRET_LISTJOURS] column is a virtual column of another
date list table

I don't understand where my mistake is.

Thank you for your help

[Suvrutt_Gurjar]

Could you update what is the [V_PRET_LISTJOURS] column type?

[Tjfyuuii]

List with Dates

[Suvrutt_Gurjar]

Thank you. Applying SELECT() on a list type column such as SELECT([List Column]......) creates a list of lists. So please flatten this list of lists using a SPLIT() function.

SPLIT() - AppSheet Help

Please try 

IF(IN(TODAY(), SPLIT(TEXT(SELECT(Pret[V_PRET_LISTJOURS],[Pret_VehicleId]=[_THISROW].[Vehicle_Id])), " , ")

),
"Borrowed",
"Available")

Since it is a date elements list, I suspect , you may possibly need date format conversion as well, if the SELECT(..) produces a date list with "mm/dd/yyyy" format and your standard date format is "dd/mm/yyyy". 

Please revert if the suggested expression does not work.

[Tjfyuuii]

Hi,

Indeed it seems that date formats bore me. I tried to reformat the date in the formula without success.

[Suvrutt_Gurjar · Accepted solution]

I believe you have applied TEXT() function for date at an incorrect place.

You could test the expression in stages.

Please check or share results of only  SELCET() from test panel by having the expression in a vc, so we know how the list looks. Accordingly you / we could make changes in the expression.

You could also check only TODAY() in another VC so as to know in what format it renders.

We could then make necessary changes.

[Tjfyuuii]

I had to abandon my virtual column to replace it with an enumlist type column.
Rather than reformatting my day list I reformatted Today() as follows:

IF(IN(TEXT(TODAY(),"dd/mm/yyyy"),SPLIT(TEXT(SELECT(Ready[Ready_ListDay],[Ready_VehicleId]=[_THISROW].[Vehicle_Id])), " , ")
),
"Borrowed","Available")

thanks a lot for your help

[Suvrutt_Gurjar]

You are welcome. Nice to know you have got it working.