Schedule based on values in a sharepoint list

(0) Share

Report

Posted on by dbaldwin1

Hello community,

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

The list in sharepoint contains columns:

Message Subject
Message Body
Day of week to send (Monday, Tuesday, etc.)
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

(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?

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

Sort by

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
dbaldwin1 35 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

dbaldwin1 35 on at

Like (1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No