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

Re: Excel Online date format and Filter Array issues

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

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Excel Online date format and Filter Array issues

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
Highlighted
Anonymous
Not applicable

Re: Excel Online date format and Filter Array issues

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

 

image.png

Highlighted
Super User III
Super User III

Re: Excel Online date format and Filter Array issues

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
Highlighted
Anonymous
Not applicable

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. 

Highlighted
Super User III
Super User III

Re: Excel Online date format and Filter Array issues

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

View solution in original post

Highlighted
Anonymous
Not applicable

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

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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!

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