cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Excel Online date format and Filter Array issues

Hi guys,

 

I have been struggling with this issue for a few days now.

I have searched through other content on this forum but none of the workarounds worked for me.

 

My flow is supposed to get data from one Excel data, filter array to have data only for one country for the last 7 days, and then add it to another Excel file.

 

Capture.PNG

 

1. I am having troubles formatting Excel Date into dd-MM-yyyy format, and then filtering such array

2. Not sure whether conditions I wrote are correct.

 

Any help appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @Anonymous 

 

Did you tried the filter format date to 'yyyy-MM-dd'?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

Hi @Anonymous 

 

What's the format of Excel date column?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Anonymous
Not applicable

Its format is 'Date', but the output comes as a number

 

image.png

Highlighted

Hi @Anonymous 

 

Try the below expression

 

Here I have set a Compose to map the Date column then used the below expression

 

formatDateTime(addDays('1899-12-30',int(outputs('Compose'))),'dd-MM-yyyy')

 

Thanks

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Anonymous
Not applicable

Hi,

 

I did it a little bit differently.

I formatted the time with the following expression

 

addDays('1899-12-30',int(item()?['Open status Date']),'dd/MM/yyyy')

 

 

TimeFlow_1.png

image.png

 

I just seperated these two filters, first getting the items just for one country, and then creating another array with data I need, at the same time chaging date format.

I could use 'Compose' right after 'List rows present in a table', as I had to turn on pagination - 'Compose' apparently doesn't like such output.

 

It works fine for the reports created at the end of each day just for the specific day.

 

I cannot filter data for the last 7 days, however. I tried the following:

 

image.png


Date format in my file is dd/MM/yyyy.

I tried to filter it in multiple ways (addDays, utcNow()-7, etc.) but it would either return nothing, or not really compare all data.

For example, if I want to get items for the last seven days (starting from 20/02/2020), Filter Array return all items with dates starting on 20th each month, like 20/01/2020, 22/12/2019 etc.

 

Any advise appreciated. 

Highlighted

Hi @Anonymous 

 

Did you tried the filter format date to 'yyyy-MM-dd'?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

Highlighted
Anonymous
Not applicable

Perfect! It works fine 🙂

 

I had to change both format of the date in the 'Select', and in the new 'Filter Array'.

Later on the output is being added to another Excel file, so I just made sure its date format is the one I want, dd/MM/yyyy.

 

Regards,

Casper

Highlighted
Frequent Visitor

I have tried all the above solutions, but can't seem to get it working.

 

I have raised a new post here: https://powerusers.microsoft.com/t5/Building-Flows/Format-timestamp-from-Excel-Online-to-send-mails/... 

 

Can someone please guide me?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

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.

Top Solution Authors
Top Kudoed Authors
Users online (7,358)