Change a Date Column in SharePoint list Automatically at the beginning of each month based on certain criteria

[tamim0326]

how do i change a date in a SharePoint list date column to 1st Wednesday of the month a the beginning off each month

Which rows needs to change depends of if Office Column Contains 'ONP' and Status Column is 'Active'

[Nived_Nambiar]

Hi @tamim0326 

so just to confirm- does the all items which needs to be updated need to have their date column values to be updated to 1st wednesday of month ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

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

[tamim0326]

sorry at the begining of each month they need to be updated to 1st wednesday of the month.  

for example for April 2024 it should be 3rd April 

for May 2024 it should be 1 May

for June 2024 it should be 5 June 

i hope that helps 

[Nived_Nambiar]

Hi @tamim0326 

I got that , what my question was whether the date change should be same for all items ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

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

[tamim0326]

yes if it meets the criteria they all should have the same date which is 1st Wednesday of the month

[Nived_Nambiar]

Hi @tamim0326 

You can try this approach 

assume this is the sharepoint list you want work on ,

Now we will using recurrance flow which runs at the start of month  (if possible run at start of month at time where UTC and your timezone would be on same date)

Now use get items to get list of items where Office eq ONP and Status eq Active like below

Now set a variable which stores current date ( i have used it for test purposes so to work with different dates). The value stores utcNow() expression which stores current date.

Now use apply to each to loop through each items

Expression used for date field - 

if(lessOrEquals(dayOfWeek(variables('date1')),3),addDays(variables('date1'),sub(3,dayOfWeek(variables('date1'))),'yyyy-MM-dd'),addDays(variables('date1'),sub(10,dayOfWeek(variables('date1')))))

This is quite complicated expression, but it helps to calculate the next nearest wednesday date 

It is designed based on mathematical logic 

Sun - 0

Mon-1

Tue-2

Wed-3

Thu-4

Fri-5

Sat-6

dayOfWeek() function returns the above values 🙂

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

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

[Nived_Nambiar]

Hi @tamim0326 

if it worked for you, please mark it as solution so that it helps others .

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

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

[Chriddle]

This expression returns the first Wednesday of the month for a specific date specified in a Compose named 'Date':

addDays(
	startOfMonth(
		outputs('Date')
	),
	mod(
		sub(
			10,
			dayOfWeek(
				startOfMonth(
					outputs('Date')
				)
			)
		),
		7
	)
)

[tamim0326]

compose 'date' what it should be? not sure how to write that compose. 

[tamim0326]

date column i am trying to change will have this month first wednesday which will need to change to next month 1st wednesday. 

[Nived_Nambiar]

Hi @tamim0326 

did you tried the approach which i have suggested ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

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

[tamim0326]

so basically i have the following that needs to change once the day has passed.  your automation is not giving me the correct date. 

OSP – 2nd Wednesday – 4/10 need to change to 5/8

ORP – 2nd Wednesday – 4/10 need to change to 5/8

OFO – 3rd Monday – 4/15 need to change to 5/20

ONP – 3rd Wednesday – 4/17 need to change to 5/15

OAMB – 4th Thursday – 4/25 need to change to 5/23

[Nived_Nambiar]

Hi @tamim0326 

Ok Now got it- so based on date , you need next date based on given date- as per your question it was to find the first wednesday of next month 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

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

[tamim0326]

so will you able to help me?