Power automate flow to count the anniversary

[jeyeline]

Hello Team,

I would like to create a power automated flow from the SharePoint list of employees joining date, which trigger mail to the employee with the content of remembering their work anniversary like first, second and so on.

Any idea on how it can be accomplished 

[creativeopinion]

@jeyeline I cover that scenario in this YT Tutorial: Send Emails Based on a 📆 Date Column in SharePoint with Microsoft Power Automate

In this Microsoft Power Automate tutorial, I’ll show you how to build a flow that will send a Happy Birthday email to a user based on a date column in a SharePoint list. The SharePoint list also contains a column with a Manager’s name which we’ll use to send a three-day and day of reminder to the user’s manager.

This automation will use the Filter Array action to filter out all SharePoint list items where the user’s birthday is today or in three days. This flow can apply to a variety of scenarios such as:

📅 Student Birthdays

📅 Project Due Dates

📅 Contract/Membership Renewals

📅 License Expirations

📅 Client Anniversaries

---

IN THIS VIDEO:

✅ How to Send an Email based on a Date Column in SharePoint

✅ Using the Recurrence Trigger in Power Automate

✅How to Use the Filter Array Action with multiple conditions

✅ How to Get Dynamic Content from a Filter Array Action

✅How to Get a Date Three Days from Today

✅ How to Create a Dynamic Date Based on utcNow()

✅ How to Return a Count of Items

✅ How to initialize and set a variable

✅ How to use the Send an Email (V2) action

✅ How to send test emails

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[jeyeline]

Hello @creativeopinion ,

I require filter based on year column. Also the triggered mail should show the content based on number of anniversary the current year is in.

[creativeopinion]

@jeyeline It would be helpful if you could share a screenshot of your SharePoint list. I'm unclear on what you mean by: the triggered mail should show the content based on number of anniversary the current year is in.

[jeyeline]

@creativeopinion , my share point list will contain the date of joining across each staff.

Staff 1 - Jan 15,2000

Staff 2 - Jul 1, 2021

Now I have to create a flow, where it should sent mail alert to the staff on their date of joining congratulating for their 24th anniversary on jan 15, 2024,
3rd anniversary on the specified date for second case

[jeyeline]

Hi @creativeopinion ,

 Any solution for this?

[Nived_Nambiar]

Hi @jeyeline 

Try this approach.

I have created a demo sharepoint having employee details with below columns

Now let's see how to create the flow

1. Use a scheduled flow type so to make it run everyday 

2. use get items to get all items from SharePoint list

3. Use initialize variable to initialize years variables which would be storing aniversary years later

4. use another initialize variable action to initialize rulesjson variables which will be storing suffix details to be added to numbers/aniversary years

5. use apply to each to loop through each items of get items action

next steps will be done under the apply to each loop

5.1 - use condition to check whether the current date is employee's work aniversary date ?

Expression used-  formatDateTime(items('Apply_to_each')?['DateOfJoining'],'MM-dd')

If the condition satisfies - all the below steps mentioned will be performed in yes branch

a.  Compute the number of years using an expression in compose action ( i have named the Compose action as Number of years)

Expression: -  sub(int(formatDateTime(utcNow(),'yyyy')),int(formatDateTime(items('Apply_to_each')?['DateOfJoining'],'yyyy')))

we will be assigning suffix to the numbers (like th,nd,rd) based on below rule

b. Use condition to check whether the numbers of years is multiple of 10 or it is >=11 or <=19 

if the above condition is true - use set variable to assign the number of years with suffix like below

Expression: - concat(string(outputs('Number_of_years')),'th')

if condition is false,

Then use the unit digit condition to assign the suffix 

use the expression in condition to check whether the unit digit of number of years is equal to 1 or 2 or 3 

if the above condition is Yes - use set variable action like below

Expression:  concat(string(outputs('Number_of_years')),variables('rulesjson')?[last(string(outputs('Number_of_years')))])

if condition is False - then use set variable with below expression -

Expression - 

concat(string(outputs('Number_of_years')),variables('rulesjson')?['Default'])

(Both of the expressions used in set variable in above yes/no branch are using data from rulesjson variable to assign suffix based on the unit digit of number of years value)

c. use send email to send email to required person/employee using details from sharepoint like below

Results of the flow

if the employee date of joining is Feb 25 2001 -> then bot will send an email like this

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[creativeopinion]

@jeyeline Sorry for the delay. You can follow the YT Tutorial that I've shared with you in a previous post. The only adjustment you would need to make is to add a few actions calculate the anniversary. 

For demonstration purposes. I've stored a sample start date into a Compose action and Today's Date in another. You should already have a Compose action in your flow with Today's date (if you've followed the tutorial). In your flow you'll need to pull the correct dynamic content from your SP list.

Get the Years

Add a Compose action to store the year from the employee' start date. Use the formatDateTime() function. 

Insert the dynamic content that has the employee's start date. In my flow, I'm using a Compose action—so I've inserted that.

Add a comma and single quotes. Between the single quotes enter:

yyyy

This will output the year only.

Wrap the entire expression in an int() function. We'll use this function to convert the year into a number.

Don't forget the closing parenthesis.

Repeat the steps above to get today's year.

Don't forget to wrap the entire expression in the int() function.

Run a test. The Compose actions should output the year from the employee's start date and today's date. Then numbers should be green which will indicate they are integers.

Calculate the Difference

Add another Compose action to store the difference between the two years. Add an expression and use the sub() function. The sub() function will subtract two numbers.

Insert the output from the Compose action storing today's year.

Add a comma.

Insert the output from the Compose action storing the employee's start date year.

Run a test. Review the output to ensure the calculation is correct. You can now insert the output from this Compose action in your Send an Email action. 

Hope this helps!

You might be interested in this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[jeyeline]

Hello @Nived_Nambiar ,

I tried with the mentioned flow but encountered errors. Attaching the flow here for reference. Can you guide us in getting it done?

[Nived_Nambiar]

Hi @jeyeline 

I think flow design is correct, but could you let what is the error you are facing ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[jeyeline]

@Nived_Nambiar , Please find the error

[Nived_Nambiar]

Hi @jeyeline 

just check whether date column from the sharepoint has any values as null ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[jeyeline]

Hi @Nived_Nambiar , 
the SharePoint list contains a date field column with values. No null values are there.

[Nived_Nambiar]

Hi @jeyeline 

could you share the expression used in the condition action ? I think column name has changed.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[jeyeline]

Hi @Nived_Nambiar ,

 Thanks for your attention. I have attached all the conditions in the attached zip file for your reference.

Let me know the things to be corrected after looking into it.

[Nived_Nambiar]

Hi @jeyeline 

There is some problem while opening the zip file. Could you share how you have written the condition in a screenshot, that would be helpful.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[jeyeline]

@Nived_Nambiar , I have applied the precise condition that was provided to me. Since the number of SS (screenshots) has exceeded, I have compressed them and shared.

I will share the content in text format once i copied all

[Nived_Nambiar]

Hi @jeyeline 

Got it now. So when looking at screenshots- i could see you have exactly used simmilar to mine. Just to confirm, does the Column - Date Of Joining has different name in your list? 

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[jeyeline]

Hi @Nived_Nambiar , I have used the column name as 'Date Of Joining'

Hope it will be helpful to investigate further

[Nived_Nambiar]

Ok, But to confirm, could you confirm whether that is DateOfJoining is its internal column name ?

https://www.axioworks.com/support/how-to/find-the-internal-name-for-a-sharepoint-column#:~:text=The%....

Also there are 3 records in list which you are iterating through right ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌