Answered

Birthday reminder with reminder sent earlier if holiday or weekend

(0) Share

Report

Posted on by mclim

I want to have a birthday/anniversary reminder that sends on workdays. Birthdays are stored on a MS List. Screenshot 2023-06-23 at 2.28.16 PM.png .

First logic group checks that it falls on a weekday. 2nd group check for those that fall on Sat and last group check for those that fall on Sunday.

I have solved the issue whereby reminder is sent on Friday if it falls on Sat or Sun. As seen in cover photo.

However, if it falls on a holiday, what logic should I use? TIA.

Categories:

General topics

I have the same question (0)

All responses (14)

Answers (1)

ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@mclim

I suggest adding all holidays for the next few years to a SharePoint list.\

Add a final check before the email is sent that compares the that you'll be sending the email to the holiday list. If the date is in the holiday list, take action as appropriate. If the date is on a weekend, just send the email. Otherwise add some logic based on the day of the week.

Was this reply helpful? Yes No
mclim 53 on at

Like (0)

Report

@ScottShearer , I think I was not clear the first time. If the birthday is on a weekend, send it on the Fri just before. Hope that clarifies. I am stuck on the logic needed to make it work.

Was this reply helpful? Yes No
abowen 222 on at

Like (0)

Report

In your OP you say you have solved the weekend issue and are asking for assistance on the holiday issue, yes? I believe Scott has answered your question. Just use the same logic excluding weekends but exclude holidays instead (after implementing Scott's solution, holidays in SharePoint list).

Was this reply helpful? Yes No
mclim 53 on at

Like (0)

Report

My algorithm -
If utcNow() is birthday, and it's not Sat,Sun, send greeting.
If utcNow() + 1 is birthday, and its Fri, send greeting
If utcNow() + 2 is birthday, and its Fri, send greeting.

@abowen , but this algorithm cannot be applied for holiday as holiday can be two days in a row and holidays can fall on weekend too. It works for weekend though.

Let me know what is the algorithm you have in mind? Thanks.

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report
@abowen

AFTER you have applied your algorithm and before the email is sent:

Lookup the date the that the email is to be sent in a SharePoint list that contains all holidays

If the date the email is to be sent is on a holiday, add additional logic to handle it

Example: if the holiday is on Tuesday - Friday, send the email the previous day

Example: If the holiday is on a Monday, send the email the previous Friday.

Hope this helps...

Was this reply helpful? Yes No
Verified answer

Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report
I created a flow using the simplest algorithm I can think of:
Given a date, subtract a day until it is Mon-Fri and is not in the holiday array.

Initialize variable reminderDate
addDays(outputs('Date'), 1)

Do Until
@And( contains(createArray(1, 2, 3, 4, 5), dayOfWeek(variables('reminderDate'))), not(contains(outputs('Holidays'), variables('reminderDate'))) )

NewDate
addDays(variables('reminderDate'),-1,'yyyy-MM-dd')

Set variable reminderDate
outputs('NewDate')

Was this reply helpful? Yes No
mclim 53 on at

Like (0)

Report

@Chriddle , I will test out your algorithm against 2 consecutive days of holiday. I reviewed your algorithm a few times but still couldn't figure out whether it would work or not...algorithms is not my strongest point.

I am a newbie to powerautomate. May I know what function you used for "Date" and "Holidays"? Thanks.

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report

"Date" simply contains the date for which you want to calculate a reminder date.
For my convenience I wrote it in a Comose action.
"Holidays" is also a Compose action and contains an array of holiday dates - here are just a few example dates that do not represent real holidays. In your flow, they should probably come from a list where you keep the holidays.

Was this reply helpful? Yes No
mclim 53 on at

Like (0)

Report

Thanks @Chriddle . I copied your program as-is.

But when I use Date “2023-08-07” and Holidays “2023-07-05”, “2023-08-05”, “2023-08-06” and “2023-08-07” which is identical to your input, the result I got is 2023-08-07 which is a holiday.

Am I missing something? What is your output? TIA.

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report

I get 2023-08-04
Maybe you should check your flow again 😉

Was this reply helpful? Yes No