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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,255)