cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SchednNedn
Frequent Visitor

Reminder Flow 2 days before due date

Hey Guys,

 

im trying to build a Reminder Flow wich is linked to my Excel Online (Business) 

 

FLO1.png

 

So in this step i Compose my Excel "Date" data into a format wich i can use. 

So fine so good, I get every Date I want in an Normal Date format. 

 

FLO2.png

 

This are the Compose actions from top to down : 

 

if(empty(items('ApplyEach_Dates')?['Datum']),'',addDays('1899-12-30',int(items('ApplyEach_Dates')?['Datum']),'yyyy-MM-dd'))

 

Here im trying to filter the Empty Fields in my Date Row and it should give me the Dates. 

 

formatDateTime(utcNow(),'yyyy-MM-dd')

 

This one captures the current time

 

if(empty(items('ApplyEach_Dates')?['Datum']),'',addDays(outputs('ComposeWorkDate'),2, 'yyyy-MM-dd'))

 

here it gets the Dates from the Date Row again and adds +2 days to it. 

 

So im not sure how i should do my conditions so my Co Workers get an Email 2 Days before the Due Date comes. 

 

Here i tried the Condition : 

 

outputs ('ComposeWorkDate') is less than  outputs('Compose')

 

and

 

Condition 2: 

 

outputs('Compose_2') is less than or equal outputs('Compose')

 

 

SchednNedn_0-1625747641376.png

 

I always get the entries with the Co Workes with current Dates

what am i doing wrong here ? 

 

Bg

Scherwin

3 REPLIES 3
tom_riha
Super User
Super User

Hello @SchednNedn ,

maybe you could do the filtering directly using the Filter Query in the 'List rows present in a table' action.

The assumption is, that Excel will provide you the date as a sequence number. Therefore, if you were able to turn today's date + the 2 days in advance into a sequence number, you could use a Filter Query

Datum eq 'Today+2days sequence number'

You can take the sequence number for today by calculating a difference between today's date and the 1899-12-30 (as used for the conversion of the Excel date number to actual date).

Calculate: ticks(...) for utcNow() minus ticks(...) for the date of '1899-12-30' as used in the conversion from Excel sequence number to a date. 1 tick is 100 nanoseconds, so you should turn it into days by dividing the result by 864000000000.

div(sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30'))),864000000000)

That's the sequence number for today. Now, you want the rows with date in 2 days from today, you should increment the result by 2.

add(div(sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30'))),864000000000),2)

And this is an expression you can use in the Filter Query and which should return only the rows with date 2 days from today. You can then send email to all the rows it returns, without any additional conditions.

Datum eq '@{add(div(sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30'))),864000000000),2)}'

 image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

Hi @tom_riha 

 

I tried to follow your steps. 

 

I composed it giving me these Outputs : 

 

FLO4.png

Flo6.png

 

So im getting these Numbers, but how do i use these Numbers to create an Email that gets send out 2 days before ? 

Im trying to find a Email Connector where i can time this somehow. 

Sorry if i did your suggestion wrong, im a newbie at this Flow thing 🙂 

 

Thanks for your quick reply anyway, I really appreciate it ! 

 

Bg

Scherwin 

You should use the scheduled trigger instead of a manual trigger. Manual trigger must be always triggered manually, scheduled will run automatically on a predefined schedule.

Create a new flow with 'Scheduled trigger', schedule the flow to run once a day, list all the rows that need a reminder, and send the reminder email.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors
Users online (1,666)