Building a scheduled flow to send a reminder email on the date which is 5 working days before end of each month

[dickyocean]

Hi,

I would like to build a scheduled flow for sending a reminder email on the date which is 5 working days before end of each month, i.e. 22/01/2021, 19/02/2021, 24/03/2021... etc. I am new to Power Automate and cannot achieve this recurring setting with built-in recurrence action. Grateful if any ideas or solution can be provided. Thanks a lot. 

[v-bofeng-msft]

Hi @dickyocean :

Please try this flow:

Set the trigger frequency of the trigger to every day, and then set the conditions for this trigger.

Trigger condition:

@equals(formatDateTime(utcNow(),'yyyy-MM-dd'), 
formatDateTime(
if(
 equals(dayOfWeek(addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-1)),5),
 addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-5),
 if(
 equals(dayOfWeek(addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-1)),6),
 addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-6),
 addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-7)
 )
),'yyyy-MM-dd'))

Best Regards,

Bof

[dickyocean]

Hi @v-bofeng-msft ,

Thanks for your reply and idea. I created an experimental flow for trial on 22/1. Meanwhile, may you please explain a little bit about your trigger condition? I try to learn but I cannot read it. Thanks again!

[v-bofeng-msft · Accepted solution]

Hi @dickyocean :

The function of this formula is to judge whether today is the last fifth working day of the month.

Get today's date

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

Get the fifth working day from the end of the month

formatDateTime(
if(
 equals(dayOfWeek(addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-1)),5),
 addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-5),
 if(
 equals(dayOfWeek(addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-1)),6),
 addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-6),
 addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-7)
 )
),'yyyy-MM-dd')

The principle is:

1. Get the last natural day of the month first
2. Determine the day of the week

• case1: This natural day is Saturday, so you need to subtract 6 days from the first day of the next month
• case2: This natural day is Friday, so you only need to subtract 5 days from the first day of the next month
• case3: In other cases, you need to subtract 7 days from the first day of the next month

This formula is to get the last natural day of the current month.

addDays(startOfMonth(addDays(startOfMonth(utcNow()),32)),-1)

 This formula is to get the the first day of the next month.

startOfMonth(addDays(startOfMonth(utcNow()),32))

 Best Regards,

Bof

[dickyocean]

Hi @v-bofeng-msft ,

Sorry for late reply and thank you for your explanation! The trial flow works successfully. Thanks a lot!

Best regards,

Dicky