Schedule based on values in a sharepoint list

[dbaldwin1]

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

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

[SudeepGhatakNZ]

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.

[dbaldwin1]

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 · Accepted solution]

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.