cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

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

 

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
Highlighted
Community Support
Community Support

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

 

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
Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Top Solution Authors
Top Kudoed Authors
Users online (6,307)