cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
abm
Super User III
Super User III

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 blogPower Automate Video Tutorials

View solution in original post

7 REPLIES 7
abm
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 blogPower Automate Video Tutorials
Anonymous
Not applicable

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

 

image.png

abm
Super User III
Super User III

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 blogPower Automate Video Tutorials
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. 

abm
Super User III
Super User III

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 blogPower Automate Video Tutorials

View solution in original post

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

HarshavardhanG
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
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (43,756)