Recurrent flow that runs on the last Monday every second quarter

[JRyder]

Hello,

Hoping someone can help. I have a flow that I'd like to run on the last Monday of every second quarter (i.e., the last Monday every 6 months, starting on the third quarter). This is my current setup: 

I'm not sure about the trigger condition that I've set (I found this example in another thread but it wasn't specific to every second quarter). Will this flow work as desired, or do I need to tweak anything?

Thank you for any help!

[wskinnermctc]

I don't know what that trigger condition will do for you. It is saying if the date is less than the 8th of a month then the flow can run.

What does the date being less than the 8th do for you?

[JRyder]

Hi. Thanks very much for getting back to me. As you can probably tell, I'm quite new to Power Automate, so I'm not sure what the trigger condition will do for my flow. I just saw it posted in another thread to a similar question. If we remove the trigger condition, and keep the main recurrence settings as they are, will the flow run first on Monday the 25th Sep, and then run every 26 weeks on the next Monday? I basically need it to run on the following dates (which are all Mondays):

• Sep 25th 2023
• Mar 25th 2024
• Sep 30th 2024
• Mar 31st 2025
• Sep 29th 2025
• Mar 30th 2026
• And so on

I'm not sure if there is a solution to this. If not then I guess I'll just let it run every 26 weeks, regardless of what day it is.

Thank you!

[wskinnermctc]

A trigger condition is like an additional check that will determine if the flow should run or not.

The formula has to equal true so the flow can run.

So let's say I have a trigger "When a SharePoint Item is Created" it will run the flow everytime the sharepoint list has a new item added.

If I put a trigger condition "@equals(utcNow('yyyy-MM-dd'),'2023-09-14')"

The flow will only run when items are created when the current date equals '2023-09-14' 

So any list items created on a day other than September 14, 2023 will not trigger the flow.

That is a odd example, but trying to get the idea out there.

The expression dayOfWeek() will get the numerical day 0-6 Sun-Sat of a datetime. Monday = 1

The expression utcNow() will get the current full datetime stamp in UTC timezone. If the expression is utcNow('yyyy-MM-dd') then the output is formatted as only the date and no timestamp.

The integer int() expression converts text type to a number type. Numbers can be added or subtracted, text numbers can't be used in math.

So the condition "@equals(dayOfWeek(utcNow()),int(1))" is saying if the current day of the week equals 1 then true.

This example would allow a flow to trigger every Monday.

The difficulty with your flow is trying to narrow down a conditional formula logic that relies on something not standard such as a quarter. Months, Days, Dates, Years are all things that could be logically referenced. 

A quarter isn't as easily targeted.

Traditional calendar quarters are:
Dates for Q1: January 1 – March 31
Dates for Q2: April 1 – June 3
Dates for Q3: July 1 – September 30
Dates for Q4: October 1 – December 31

So I don't really understand what you mean by every second quarter starting in the third. I think I do, but I'm going to use the traditional quarter calendar reference along with your date list.

Sep 25th 2023
Mar 25th 2024
Sep 30th 2024
Mar 31st 2025
Sep 29th 2025
Mar 30th 2026

To me it looks like you want the flow to run the last Monday of the first quarter and the last Monday of the third quarter. Does that seem correct?

[JRyder]

Firstly, thanks for your continued help with this query, and for the useful information on conditions. It's really appreciated.

You're correct in your thinking, and apologies for not being clearer. It might help if I provide some context. My department gives out a star award every 6 months for Q1 and Q3, which employees can nominate other employees for. Management wants to send an email and Teams notification to employees on the last Monday of Q1 and Q3 to let them know that the nomination window is open. Employees then have 2 weeks to submit their nominations, and following that, management has two weeks to decide the winner. The winners are announced mid-way through the Q2 and Q4 quarters.

From what you're saying, working with quarters might not be possible. That's fine if that's the case as I appreciate there are limitations. Do you have any suggestions for a solution that's the next best fit, based on the above context? I guess I could just configure it to send out the email every 6 months. Of course, that would mean the email and Teams notification getting sent out a week before the last Monday of the quarter in some cases, and in some cases the flow will get triggered on a weekend.

I'm not sure how the On these days field works. I previously selected Monday, but I'm guessing that the flow will not run at any 6-month interval that doesn't fall on a Monday, so I've disabled that parameter. A solution to tell the flow to wait until Monday when it's initiated every 6 months would be ideal, i.e., run the flow every 6 months but if it's not a Monday then wait until it is Monday before you trigger the actions. That's probably not possible though.

Here's my current flow:

In case you're wondering, the Add to time actions are used to insert future deadline dates into the emails and Teams posts.

Thanks again for your assistance. Don't worry if you don't have any better solutions though as I'm conscious of taking up too much of your time on a potential pie-in-the-sky idea.

[wskinnermctc · Accepted solution]

Alright this is going to be fun. I came up with a solution, but when I got done, I realized that a flow that sits for 6 months will automatically be disabled. So after my primary solution, I have a secondary that will adjust for the 6 month delay.

So think of this as 2 parts:

• Solution to the Trigger Condition.
• Follow-up amendment to keep flow active for 6 months. 

I'm adding extra detail so you can understand how the flow works so you can make changes or test dates.

Solution for Trigger Condition - Last Monday of 1st Quarter and Last Monday of 3rd Quarter:

I recommend you make another scheduled flow that sends an email to you, and adjust the dates in the trigger condition to something like next week to verify it works correctly with a small test flow.

OR(AND(greaterOrEquals(convertfromUtc(utcNow(),'Central Standard Time'),addDays(startofMonth(utcNow('yyyy-04-01')),-7)),less(convertfromUtc(utcNow(),'Central Standard Time'),startofMonth(utcNow('yyyy-04-01')))),AND(greaterOrEquals(convertfromUtc(utcNow(),'Central Standard Time'),addDays(startofMonth(utcNow('yyyy-10-01')),-7)),less(convertfromUtc(utcNow(),'Central Standard Time'),startofMonth(utcNow('yyyy-10-01')))))

This will be based upon the traditional calendar quarters:

• Dates for Q1: January 1 – March 31
• Dates for Q2: April 1 – June 30
• Dates for Q3: July 1 – September 30
• Dates for Q4: October 1 – December 31

General Idea:

• Flow scheduled to run weekly every Monday
• Get Date Range of Last Week of Quarter 1
  • Determine if Current Date is within last week of Quarter 1
• Get Date Range of Last Week of Quarter 3
  • Determine if Current Date is within last week of Quarter 3
• If the date is within either week range, trigger the flow.

The basic idea I am using for the Last Monday of 1st Quarter and Last Monday of 3rd quarter is by hard coding the dates beginning of the 2nd quarter (April 1st) and beginning the 4th quarter (Oct 1st).

Subtract 7 days from the quarter start dates and it will be the last week of the previous quarter.

April 1st - 7 days = last week of 1st quarter

October 1st - 7 days = last week of 3rd quarter

A key component to this is the Flow Recurrence Schedule will be set for Monday. The flow will be created to run Weekly on Monday. If there were no trigger conditions then the flow would run every Monday.

Schedule the Recurrence to Weekly - Monday

Since the flow will be scheduled to run every Monday, we can use the current time as a point of reference. 

Every Monday the flow will ask the questions:

Q1: Is the current datetime within the last week of the 1st Quarter?

Or

Q2: Is the current datetime within the last week of the 3rd Quarter?

If either Q1 OR Q2 are true, then the flow needs to continue running.

Trigger Condition Expression:

This will be the full trigger condition to use:

OR(AND(greaterOrEquals(convertfromUtc(utcNow(),'Central Standard Time'),addDays(startofMonth(utcNow('yyyy-04-01')),-7)),less(convertfromUtc(utcNow(),'Central Standard Time'),startofMonth(utcNow('yyyy-04-01')))),AND(greaterOrEquals(convertfromUtc(utcNow(),'Central Standard Time'),addDays(startofMonth(utcNow('yyyy-10-01')),-7)),less(convertfromUtc(utcNow(),'Central Standard Time'),startofMonth(utcNow('yyyy-10-01')))))

You need to replace 'Central Standard Time' with your time zone.

This trigger condition uses these expressions:

• or()
• and() 
• greaterOrEquals()
• convertfromUtc()
• utcNow()
• startofMonth()

The full expression above is basically 2 identical sub expressions with the only difference being that one is hard coded for April 1st and the other is hard coded for October 1st. The OR() wraps both expressions and will return true if either of the sub expressions return a value of true.

OR({Subex-A},{Subex-B}) - OR is true if either Subex-A or Subex-B are true

Subex-A: Sub expression to check if current date is within last week of 1st Quarter (April 1st - 7 days): 

AND(greaterOrEquals(convertfromUtc(utcNow(),'Central Standard Time'),addDays(startofMonth(utcNow('yyyy-04-01')),-7)),less(convertfromUtc(utcNow(),'Central Standard Time'),startofMonth(utcNow('yyyy-04-01'))))

Subex-B: Sub expression to check if current date is within last week of 3rd Quarter (Oct 1st - 7 days):

AND(greaterOrEquals(convertfromUtc(utcNow(),'Central Standard Time'),addDays(startofMonth(utcNow('yyyy-10-01')),-7)),less(convertfromUtc(utcNow(),'Central Standard Time'),startofMonth(utcNow('yyyy-10-01'))))

I'll break down the expression using the Subex-A as an example:

• AND({1subex}, {2subex}) - AND is true if both 1subex and 2subex are true

1subex: asks if the current datetime is greater than or equals April 1st of the current year subtracted by 7 days

greaterOrEquals(convertfromUtc(utcNow(),'Central Standard Time'),addDays(startofMonth(utcNow('yyyy-04-01')),-7))

2subex: asks if the current datetime is less than April 1st of the current year

less(convertfromUtc(utcNow(),'Central Standard Time'),startofMonth(utcNow('yyyy-04-01')))

Time Zone Conversion

For this to work correctly you need to convert the time zone of the utcNow() into your local time.

First part will be dealing with the current datetime when the flow is scheduled. It will be scheduled for 9AM Monday for your timezone. Since we are using utcNow() to get the current datetime, we need to convert that into your timezone by using convertfromUtc(utcNow(),'{your time zone}') expression.

Since I am in United States Central Time, I would use:

convertfromUtc(utcNow(),'Central Standard Time')

You can find the name to use for your time zone by checking the Windows Default Time Zones page. Or you can just get an example flow step "Convert time zone" and in the action settings select the Peek Code to look and see what the name would be for your timezone.

How to identify the text timezone needed for convertfromUtc() expression

Recurrence Example:

This would be if you are located in the Berlin time zone:

Recurrence - Berlin TIme Zone

Recurrence Settings - Berlin Time Zone

This is the expression that would be used in the trigger condition for Berlin time zone which is written as 'W. Europe Standard Time' for the conversion.

OR(AND(greaterOrEquals(convertfromUtc(utcNow(),'W. Europe Standard Time'),addDays(startofMonth(utcNow('yyyy-04-01')),-7)),less(convertfromUtc(utcNow(),'W. Europe Standard Time'),startofMonth(utcNow('yyyy-04-01')))),AND(greaterOrEquals(convertfromUtc(utcNow(),'W. Europe Standard Time'),addDays(startofMonth(utcNow('yyyy-10-01')),-7)),less(convertfromUtc(utcNow(),'W. Europe Standard Time'),startofMonth(utcNow('yyyy-10-01')))))

All of the above should get the schedule to run only on Mondays of the last week of the 1st and 3rd annual quarters.

Follow-up amendment to keep flow active for 6 months:

Since Power Automate will disable a flow that has not been run in 60 days, you need to find a way to get it to stay active until you actually want it to run twice a year.

I recommend adding 3 additional things to the primary trigger condition:

• Schedule the Recurrence for Sunday and Monday
• Add additional expression to trigger condition to allow runs on Sunday
• Insert a Condition control into the flow - to check if the day is Sunday or Monday
• If the day is Sunday then send you a personal email note
  • Followed by a Terminate step

Adjust the schedule to include Sunday:

Add expression to the trigger condition that allows the flow to run on Sundays: 

OR({Subex-A},{Subex-B},{Subex-C) - OR is true if either Subex-A or Subex-B or Subex-C are true

Subex-C: Sub expression to check if current day of week is Sunday

equals(dayOfWeek(convertfromUtc(utcNow(),'W. Europe Standard Time')),int(0))

dayOfWeek() expression gives a number zero 0 for the day Sunday. Sun-Sat:0-6

Including this with the main trigger condition OR will look like this as the entire condition:

OR(AND(greaterOrEquals(convertfromUtc(utcNow(),'W. Europe Standard Time'),addDays(startofMonth(utcNow('yyyy-04-01')),-7)),less(convertfromUtc(utcNow(),'W. Europe Standard Time'),startofMonth(utcNow('yyyy-04-01')))),AND(greaterOrEquals(convertfromUtc(utcNow(),'W. Europe Standard Time'),addDays(startofMonth(utcNow('yyyy-10-01')),-7)),less(convertfromUtc(utcNow(),'W. Europe Standard Time'),startofMonth(utcNow('yyyy-10-01')))),equals(dayOfWeek(convertfromUtc(utcNow(),'W. Europe Standard Time')),int(0)))

This will allow the flow to run on Sundays every week.

Add Condition and Terminate within the flow:

You can create a variable and put the exact same expression that checks the day of the week. And put that variable in a condition if equal "True" then it is Sunday. Add a terminate step that will Cancel the flow on the Yes side of the condition. 

Terminate will stop the entire flow. 

You can put your primary flow under the condition, you do not have to put it in the No side. 

Add Condition into Flow to Stop it from completing on Sundays

Well I hope this works out for you. Put it together and let me know if you have any questions.

[JRyder]

Genius! You actually worked out a solution. I'm really impressed with the logic that you've applied. Yesterday, I was working out how to relay the bad news to management. Today, I am looking forward to delivering the solution. 

I'm going to have fun putting this together today. It all makes sense, so I don't see any issues at this point, but thanks for offering to answer any further questions if I run into any problems. In the meantime, I am happy to mark this as solved.

I can't tell you how much I appreciate your help with this, especially with you going the extra mile to explain things in detail. I really love logic and problem-solving, but I would never have got anywhere close to a solution by myself. Thank you so much! If you have a Buy Me a Coffee link, or something similar, let me know. And if I remember then I'll circle back to this in 6 months time to confirm its success.

[wskinnermctc]

Hope it helps! If it was me, I would make another simple flow and work on the dates to verify how it working with the Recurrence scheduler. Like change the date range checker to be September 1st and add 7 days. See if it runs on Monday.

(I kinda don't like the scheduler because it is a little confusing how it works. Like it asks for datetime zone, but then start time doesn't seem to do what I want, so I always leave it blank.)

At the least, you could not use the expression in the trigger condition. Instead of using the trigger condition, put the expression into the flow in a similar way that I did with the Sunday checker.

If you did it that way, then it would literally run every Monday, but you have a condition to check if the full expression is true, if it is, run the primary flow, if not, terminate.

NO Trigger Condition - The expression that checks the correct Monday will be in the flow

The expression is the main solution expression that just checks the Monday, it doesn't have the Sunday day of week expression included since it wouldn't be necessary:

OR(AND(greaterOrEquals(convertfromUtc(utcNow(),'Eastern Standard Time'),addDays(startofMonth(utcNow('yyyy-04-01')),-7)),less(convertfromUtc(utcNow(),'Eastern Standard Time'),startofMonth(utcNow('yyyy-04-01')))),AND(greaterOrEquals(convertfromUtc(utcNow(),'Eastern Standard Time'),addDays(startofMonth(utcNow('yyyy-10-01')),-7)),less(convertfromUtc(utcNow(),'Eastern Standard Time'),startofMonth(utcNow('yyyy-10-01')))))

If you did it this way, you do not need the Sunday checker because the flow will be running every Monday. No need to worry about it being disabled for inactivity.

Honestly, I like this approach more.

It is transparent of what is happening. And depending on your Power Automate subscription plan, it isn't a big deal to trigger this flow, since it is not using up a lot of api just to check a condition and then terminate the flow. If you have to pay per flow run or something, then the trigger condition will save you unnecessary flow runs.

But we are talking about 1 flow run per week. So even that's not a huge deal.

(Sadly I did not answer this post within 24 hours, so I don't get fake internet points that count towards the Summer of Solutions Contest dashboard. 😭😭😭 But knowing my solution found a good home makes it all worth it! 😊)

Good luck!

[JRyder]

It definitely helps! My only concern is that management will think that I'm a PA expert now, and I'll start receiving more and more complex flow requests. I guess I'll worry about that if it comes to it, although I've already told one of the managers that I've had to recruit an expert to help with this.

I also really like your revised solution of having the expression within the flow. If I get knocked over by a bus then at least someone will be able to see what's going on more easily. I'm going with your revised version now so thanks very much. I'll be sure to test everything out before we go live.

I'm really sorry to hear that you won't get any "fake internet points" as a result of this. I'm absolutely blown away by how much effort you've put into helping a stranger out. Rest assured that your effort here will make a big difference to a lot of people in my company. They're actually really excited to get this flow up and running. And of course, I'll be able to use what you've taught me here to create similar flows, so your spirit will live on. Once you start automating tasks, people start to wonder what other tasks can be automated.

Once again, thank you so much for your assistance. I could not have asked for better help, and I'll make an effort to remember to let you know how the flow gets on. Thanks!