ODATA Filter query based on date from excel onedrive

[Santhosh72]

Hi,

Beginner needs help to filter data from excel table in onedrive using ODATA filter query based on date field in excel. Query will be on "List rows present in a table" option. I understand excel returns a string as number for dates stored in excel. How can I compare this number string against utcNow to filter data.

Thanks

Santhosh

[efialttes · Accepted solution]

Hi!

In my example, the excel column con Date format is called 'TestingDate'

Here you are the expression I use in my ODATA filter to filter records whose TestingDate is today:

TestingDate eq 'add(div(sub(ticks(utcNow('MM/dd/yyyy')),ticks('1900/01/01')),864000000000),2)'

Please note I am adding an expression inside single quotes this way:

Hope this helps

[Santhosh72]

Thanks, It worked.

Grateful if you can also help me with my previous question https://powerusers.microsoft.com/t5/Building-Flows/Consolidated-email-based-on-date-in-excel/m-p/471...

[Santhosh72]

Hi,

How can I use "greater than" or "less than" operator instead or "eq" operator in the filter?

Thanks

Suji

[RobElliott]

gt = greater than

ge = greater than or equal to

lt = less than

le = less than or equal to

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Santhosh72]

Dear @RobElliott ,

Since we are comparing two strings, I dont think these will work. I tried and got unsupported operator error.

Thanks

Santhosh

[RobElliott]

I hadn't read the rest of the thread when I posted, just your most recent question. It is, as you have realised, not possible or logical to say greater than or less than a string value.

Rob
Los Gallardos

[Santhosh72]

So is there any other way of filtering excel dates using less than or greater than operator?
Thanks
Santhosh

[RobElliott]

Yes there is using the ticks expression (nanoseconds since 1st January 1601) but I don't think there's a way of doing this with a filter query, you have to do it with a series of Compose controls in the body of the flow.

After your List rows present in a table, the first control inside the apply to each is a Compose to get the date from each item in the Excel spreadsheet:

Next, inside another Compose turn that into an integer using int(outputs('Compose_Date')). Next, calculate the number of ticks of the integer by doing a multiplication inside another Compose using
add(mul(outputs('Compose_Integer'),864000000000),599264352000000000)

Next, add another Compose to get the ticks value from the start of today using the expression ticks(startOfDay(utcNow()))

Finally, add a condition. In my example I'm checking of the date column in the spreadsheet is greater than today's date, i.e in the future.

The flow will run and do a comparison of the date in the Excel column against today's date.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.