cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KacperHolakSGS
Level: Powered On

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
Super User
Super User

Re: Excel Online date format and Filter Array issues

Hi @KacperHolakSGS 

 

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!



View solution in original post

6 REPLIES 6
Super User
Super User

Re: Excel Online date format and Filter Array issues

Hi @KacperHolakSGS 

 

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!



KacperHolakSGS
Level: Powered On

Re: Excel Online date format and Filter Array issues

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

 

image.png

Super User
Super User

Re: Excel Online date format and Filter Array issues

Hi @KacperHolakSGS 

 

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!



KacperHolakSGS
Level: Powered On

Re: Excel Online date format and Filter Array issues

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. 

Super User
Super User

Re: Excel Online date format and Filter Array issues

Hi @KacperHolakSGS 

 

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!



View solution in original post

KacperHolakSGS
Level: Powered On

Re: Excel Online date format and Filter Array issues

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

Helpful resources

Announcements
firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,926)