cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ikmalfikri
Helper I
Helper I

Filtering data from Excel and sending email

Hi,

I have problem with this. So the idea is i have an excel document on OneDrive with data is fed through Forms. Now i want to filter the data based on 'From' and 'Until' field date. The idea is from<=currentdate and until>=currentdate then a table is created to be used for mail.

So i use list rows present then filter array. But on filter array, the date from excel sent to the filter messed up changing into weird format like 44116 (supposed to be 10/12/2020)..Now how can i fix this?

@lessOrEquals(formatDateTime(item()?['From'], 'yyyy-MM-dd'), utcNow('yyyy-MM-dd'))

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

@ikmalfikri 

Hi there.  So, Excel dates are...weird, and you're not alone.  Here's an article from @shanebart  on integrating Excel dates in with the platform:

 

Handling Excel Dates in Microsoft Power Automate

 

Keep us posted.

-Ed

View solution in original post

5 REPLIES 5

@ikmalfikri 

Hi there.  So, Excel dates are...weird, and you're not alone.  Here's an article from @shanebart  on integrating Excel dates in with the platform:

 

Handling Excel Dates in Microsoft Power Automate

 

Keep us posted.

-Ed

View solution in original post

hi..but then how do i use filter array to filter the data based on the date in the excel and the current date

@ikmalfikri 

Hi there.  Give this a go:

 

@lessOrEquals(addDays('1899-12-30',int(item()?['From']),'yyyy-MM-dd')), utcNow('yyyy-MM-dd'))

 

Basically, we're swapping out the current Excel "From" date, with the addDays and the number that's in the "from" field.

 

Keep us posted.

-Ed

 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Hi..

I've updated it using 'Apply to each' and 'Condition'

for the date ;

@lessOrEquals(addDays('1899-12-30',int(item()?['From']),'yyyy-MM-dd')), utcNow('yyyy-MM-dd'))

 

but it didn't seem to work..even though i filter the date, the data still considered true..
Eg;

date in excel ; 20/10/2020
utcnow ; 19/10/2020 
but the date with that date still considered true...or is condition didn't work?121720871_333691294590661_4056019370636571970_n.jpg

Nevermind...I detected the problem..My mistake..

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,028)