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

Schedule based on values in a sharepoint list

Hello community,

 

I am building a monthly reminder system using a sharepoint list and powerautomate.

 

The list in sharepoint contains columns:

  1. Message Subject
  2. Message Body
  3. Day of week to send (Monday, Tuesday, etc.)
  4. Occurrence of day to send (1, 2, 3, 4)

The flow will be configured to run daily and if the current day matches the logic of "day of week" and "Occurrence of day" the condition will evaluate to True and the list item will be emailed.

 

I am able to get the day name using 

string(formatDateTime(body('Convert_time_zone'),'dddd'))

and match with this

dbaldwin1_1-1667856472689.png

(still refining this)

 

I have been unable to find a way to get the occurrence of the day in the month.  Such as 1st Monday or 3rd Tuesday.

 

Is it possible to get the occurrence of the day in the current month like this?

1 ACCEPTED SOLUTION

Accepted Solutions
dbaldwin1
Frequent Visitor

I was able to get this working using this formula:

 

formatdatetime(adddays(startOfMonth(body('Convert_time_zone')),add(mul(sub(int(items('Apply_to_each')?['DayOccurrence/Value']),int(greaterorequals(int(items('Apply_to_each')?['DayofWeektoSend/Value']),DayOfWeek(startOfMonth(body('Convert_time_zone')))))),7),sub(int(items('Apply_to_each')?['DayofWeektoSend/Value']),int(dayOfWeek(startOfMonth(body('Convert_time_zone') ) ) )) )),'MM/dd/yyyy')

 

The "Day of Week to Send" and "DayOccurrence" columns from sharepoint are choice columns with numeric choices with 1-5 for Monday through Friday and 1-4 for first week through fourth week in the month (I leave out the fifth week since not all days have a fifth occurrence).

 

Then the flow is scheduled to run daily and has a condition to match if today is equal to the return of that formula

 

Some credit should go to this article from an excel blog, I converted this code to powerautomate

How to Find Nth Weekday in Month (contextures.com)

 

I hope this helps someone else, it was a fun challenge to figure out and the math suggestion from Sudeep got me thinking about the problem differently so I was able to look for a different way to approach a solution.

View solution in original post

3 REPLIES 3
SudeepGhatakNZ
Resident Rockstar
Resident Rockstar

If you use day of week function to find the day of 1st of the current month, you can work out the occurrences by dividing the current day by 7 (Quotient + 1) and the day will be (Remainder - 1).

Like

1 st is Monday

8th, 15th, etc will be Mondays

Any date like the 11th will be 11/7 = Q1, R4, hence it will be (1+1) occurrence of Monday + 4 - 1 = Thursday.

 

If my suggestion helped you, please give it a Thumbs up and mark it as a Solution so that it can benefit others in the community.

Hello and thank you for the suggestion.  I having a hard time following the logic to where I have an end result that gives me a number like 1, 2, 3, 4 for the occurrence of a day within the month.

 

For example, I should be able to get "2" for today November 8th the second Tuesday in the month.

 

I am also not familiar enough with the syntax for div() to get a quotient and remainder instead of a decimal result.  Is that an option?  I did not see it in the documentation.

dbaldwin1
Frequent Visitor

I was able to get this working using this formula:

 

formatdatetime(adddays(startOfMonth(body('Convert_time_zone')),add(mul(sub(int(items('Apply_to_each')?['DayOccurrence/Value']),int(greaterorequals(int(items('Apply_to_each')?['DayofWeektoSend/Value']),DayOfWeek(startOfMonth(body('Convert_time_zone')))))),7),sub(int(items('Apply_to_each')?['DayofWeektoSend/Value']),int(dayOfWeek(startOfMonth(body('Convert_time_zone') ) ) )) )),'MM/dd/yyyy')

 

The "Day of Week to Send" and "DayOccurrence" columns from sharepoint are choice columns with numeric choices with 1-5 for Monday through Friday and 1-4 for first week through fourth week in the month (I leave out the fifth week since not all days have a fifth occurrence).

 

Then the flow is scheduled to run daily and has a condition to match if today is equal to the return of that formula

 

Some credit should go to this article from an excel blog, I converted this code to powerautomate

How to Find Nth Weekday in Month (contextures.com)

 

I hope this helps someone else, it was a fun challenge to figure out and the math suggestion from Sudeep got me thinking about the problem differently so I was able to look for a different way to approach a solution.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,346)