Comparing a date to a list of dates

[icarecenter]

I have a list of dates with a y/n column.

I want to check the date in this row with another sheet that has a list of dates.

One one sheet, I have a travel log of dates.

Second sheet, I have my statement of dates that I used the toll.

I want to flag the dates I use toll on that travel log sheet.  I'm not sure how to compare a date to a list of dates.

Can somebody help?

[icarecenter · Accepted solution]

So this works but in order for it to compute, I have to open each one, edit, and save.  I don't need to change anything but it won't calculate an existing set of data.  It will probably update new data.  Is there another way where I can run this expression on a list already?

[lynchk21]

You can use IN(text-to-search-for, list-to-search) 

IN(TableName[Date Column],[_thisRow].[Date Column])

[WillowMobileSys]

It is not completely clear...but If I understand correctly, you want check, by the date in your "travel log of dates", if a toll was actually used on that date.  I will assume that the "statement of dates that I used the toll"  has a single row for EACH usage - i.e. if the toll was used 3 times on a data there are 3 separate entries in the table.

Since all you want to know is a Yes/No if the toll was used then you would implement in your flag column, on the "travel log of dates" table, an expression like this:

COUNT(FILTER("Toll Used Table Name", [travel log date] = [toll date])) > 0

Simply, are there any toll used entries for the travel log date?

I hope this helps! 

[icarecenter]

Sorry, I wasn't clear.  On the travel log, I have a list of dates that I worked.  On the tolls sheet, I have a list of dates that I used toll.  On the tolls sheet, I want to flag the date that I worked on the travel log and used the toll.   The toll sheet has all the tolls that I used but I only want to flag the tolls that I used when working.  This way I can group it and calculate the total tolls that I spent when working.  Does that help?

[lynchk21]

In a virtual column use the expression on the tolls sheet:

IN([_thisRow].[Date Column Name],travel log[Date Column Name])

[icarecenter · Accepted solution]

So this works but in order for it to compute, I have to open each one, edit, and save.  I don't need to change anything but it won't calculate an existing set of data.  It will probably update new data.  Is there another way where I can run this expression on a list already?

[icarecenter]

It worked!  It had to be a virtual column.  I don't understand the logic though.  Wouldn't it be the same thing?

[lynchk21 · Accepted solution]

Virtual columns recalculate instantly, while real columns only calculate on a sync, since they have to write the data to the source. Because of this virtual columns are actually more expensive data crunching wise, but are useful in certain scenarios.  (At least this is my understanding - someone here with more technical knowledge could provide a more thorough explanation). I am glad you got it working!