Send email before DueDate excluding weekend days

[Assepa]

Hi,

I would like to build a flow where an email is send two days before Due date(column name in my SP list) excluding the weekend days

example: if Due date in on Monday, the email must be send on Thursday or if Due date is on Tuesday, the email must be send on Friday

Is there a way to make it happen ? Thanks!!!

[ccc333ab · Accepted solution]

Here is an approach.

The Logic

What this solution does if figures out what day it is today.

• If it is a Thur or Friday, then it'll look for due dates 2 days from now, and 4 days from now. (So yes, checking for Saturday/Sunday due dates...in case of user error or you allow for weekend due dates). 
• If any other day, just looking for due dates 2 days from now 

Note: I probably could have tried to write this in a single expression, but that would be harder to follow and troubleshoot. 

Solution 

1. Start with a Recurrence trigger running every day (except for weekends)

2. Create a variable to store your filter string in so we can find those due dates 2 (or 4) days from now. 

3. Create a condition to see if today is Thursday (day of week 4) or Friday (day of week 5). 

Left hand expression is just a string to check against only containing 4 and 5. 

string('4,5')

Right hand expression is getting todays day of week. 

string(dayofWeek(utcNow()))

4. If it is a Thursday or Friday, build up filter query to add 2 and 4 days. 

concat('(StartDate eq ', '''', string(adddays(utcNow(),2,'yyyy-MM-dd')),'''', 'or StartDate eq ', '''', string(adddays(utcNow(),4,'yyyy-MM-dd')),''')')

(Note: My list has the field StartDate...you would add in your date field...i.e., due date). 

5. If no, then only include a filter query of 2 days from now

concat('StartDate eq ', '''', string(adddays(utcNow(),2,'yyyy-MM-dd')),'''')

6. And now you can use a GetItems to extract all rows in your SharePoint list with that specific due date by using your strFilter in the Filter Query box.

7. And finally you would loop through all returned records and send your email. I am just using a Compose here as an example...you would replace with your email action. 

[Assepa]

Thanks a lot for the fast reply, I will try that!!

[Assepa]

If Due date is on Sunday I will receive a reminder by email on previous Thursday right ? Thank!!!!

[ccc333ab]

Unfortunately not. I'm using the reverse logic: 

• Don't think of it as "if due date on Sunday, send email on Thursday".
• Instead, think of it as "If today is Thursday, I need to send emails if due date is Saturday or Monday". (2 or 4 days). 

So now if you add a 0 in, it will actually be testing for: 

• If today is a Sunday, send email if due date is next Tuesday or next Thursday (which is totally wrong) 

So right now, the code currently will send an email reminder on Friday for all Sunday due dates. IF you need this to go out on Thursday instead, that actually changes things quite a bit. Here is the approach for this: 

Note: It's better to use a SWITCH in this casse, but since you have this built I'll just embed a condition inside your current condition. 

1. In the Yes Condition add new Condition

2. Test if day of week is 4 (Thursday)

3. If yes, use the condition of: 

concat('(StartDate eq ', '''', string(adddays(utcNow(),2,'yyyy-MM-dd')),'''', 'or StartDate eq ', '''', string(adddays(utcNow(),3,'yyyy-MM-dd')),'''', 'or StartDate eq ', '''', string(adddays(utcNow(),4,'yyyy-MM-dd')),''')')

 which checks to see if the due date lands on Sat, Sun or Mon

4. If no (then its a Friday, use the expression of: 

concat('StartDate eq ', '''', string(adddays(utcNow(),4,'yyyy-MM-dd')),'''')

which will now only test if due date is next Tuesday. 

[Assepa]

Thank a lot ! I'm new to Power Automate so everything is new to me. Is there an order to take in consideration if I add a new condition in the Yes Condition. I mean do I have to put the new Condition above or below the Set variable strFilter to 2 and 4 days (purple box) ?

And to test if day of week is 4 (Thursday) will the condition be for the Left hand expression :

string(dayofWeek(utcNow()))

Is equal to

 And the right hand expression : 4 ?

[ccc333ab]

Flows in Power Automate have a top down flow order, so yes, you would need to do a TEST to see what day it is before you create the filter. So the YES condition would look like: 

Where the expression in the Condition here I used: 

dayofWeek(utcNow())

Note: daysofWeek expression returns an integer. Because we don't need to use a compare in this condition, we don't need to force it to be a string. 

[Assepa]

I try the flow (on july 2th=friday) with the embed condition (Condition testing if Thursday) in my current condition (Condition) but it didn't send an email for the DueDate=Tuesday july 6th. The first image is the output when I ran the flow and the second is the structure of the flow with the embed condition.

[ccc333ab · Accepted solution]

I believe you are running into a Time Zone issue...I don't know what time zone you are in, but I'm in Mountain Time and it would be Saturday in UTCTime (7 hours ahead of me). 

You can see what date UTCNow is returning to you by creating a test flow, having one Compose Statement with UTCNow() expression...I'll bet it says its Saturday. 

If you want to ensure you get the right date (if your flow you are scheduling for a night run), then either: 

• You can simply use the expression to subract the hours difference between your time zone and UTC time in the condition expression. 

string(dayofWeek(addHours(utcNow(),-7,'yyyy-MM-dd')))

• You can add in an action right at the beginning of your flow that converts the UTCNow to current time zone

(Expression is: string('yyyy-MM-dd')

EDIT: Then use this in your expression instead of UTCNow()

[Assepa]

Thank a lot!!! I forgot about the Time zone, I'm in Eastern Time (US&Canada).

[Assepa]

Hi,

I have another question for you, in the Concat expressions, the name of your field StartDate, is it the column internal name or simply the display that appears in the SharePoint list ? Thanks!

[ccc333ab]

Short Answer: StartDate would be the name of the field in your SharePoint list (internal name). 

Long winded answer: 

• To ensure you know the right name of your SharePoint field, a lot of people will tell you to do something like this (which works just fine).
• But I don't like to jump over to SharePoint when I'm working in Power Automate (call me lazy), so what I do is create a Compose statement, add the field that I'm testing as the input into that Compose field, then hover over it and see what the name is.
• For example,  I have a field called "Stage 1 Approvers". Anytime you have a column with spaces, you're going to need to find out how it was named by SharePoint internally. SO I add that to a compose statement below and I can see how it is named: 

• So now I know I would need to use Stage_x0020_1_x0020_Approvers. 

[Assepa]

Oups,I meant the internal name not the external name.

So I will need to use the name that appears 

at the end of the URL on the Edit column page, after ‘Field=’ for the concat expression.

Thank for the fast reply!!!