cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dan15
New Member

How to check if date matches Today - Excel online, condition or filter array?

Hi all!

 

I am not a developer, but I must say. building a flow that with conditions including dates is surprisingly hard. 

 

I'm trying to build a flow that checks if there is one or more lines in an Excel file has date the same as Todays date. I'v spent a good amount of hours searching the forum for solutions. I tried a lot  of them but I'm running into dead ends.

 

My question is: What is the correct way to do this in Power Automate?

 

Prerequisite:

The dates in the Excel (source) was created with the expression:

convertTimeZone(utcnow(),'UTC','W. Europe Standard Time','MM/dd hh:mm tt')

by the flow below

image.png

 

 

and the final excel result looks like this.

 

image.png

 

Try 1)

image.png

Error message: Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.

 

Try 2) 

Expression: formatDateTime(body('dateTime'),'yyyy-MM-dd') eq '@{utcnow('yyyy-MM-dd')}'

List rows in table.png

Error message: An unknown function with name 'body' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.
inner exception: An unknown function with name 'body' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.

 

 

Try 3)

image.png

Expression (in Advanced mode) : @equals( formatDateTime(item()?['dateTime'],'yyyy/MM/dd'),
utcnow('yyyy/MM/dd')
)

 

Error message:

Excel date.png

Conclusion: Excel Online action doesn't store date in a correct way.

 

Try 4)

Same as no.3 but with a new expression to convert the excel date :

Expression: @equals(formatDateTime(addDays('1900-01-01', sub(int(item()?['dateTime']), 2)), 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))

 

Error message:

image.png

 

 

How should one do to compare dates, either in filtering in the Excel Online action, Filter array or Condition?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bacao-msft
Community Support
Community Support

 

Hi @Dan15 ,

 

You need to use the expression to restore the number to date. Please check this thread and see if it helps:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-file-to-list-flow-date-issue/m-p/279258#M28...

 

Then you could use the following expression to filter rows:

@equals(addDays('1899-12-30', int(split(item()['dateTime'], '.')[0]), 'yyyy-MM-dd'), utcNow('yyyy-MM-dd'))

76.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-bacao-msft
Community Support
Community Support

 

Hi @Dan15 ,

 

You need to use the expression to restore the number to date. Please check this thread and see if it helps:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-file-to-list-flow-date-issue/m-p/279258#M28...

 

Then you could use the following expression to filter rows:

@equals(addDays('1899-12-30', int(split(item()['dateTime'], '.')[0]), 'yyyy-MM-dd'), utcNow('yyyy-MM-dd'))

76.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,093)