How-To #3: Get First Day of the Week using Power Automate

I was recently having a conversation with a friend about getting items from a SharePoint list, but these items must be filter based on the first day of the week. I know for some folks, the first day of the week is Sunday and for others is Monday, but for this scenario, the first day of the week is Sunday. And since there isn’t an action that can give us that value out of the box, I embarked on a search to find the best expression to get the desired value. For knowledge sake, I will add an expression to get both Sunday and Monday as the first day of the week. 😎

Sunday

formatDateTime(subtractFromTime(utcNow(), dayOfWeek(utcNow()), 'Day'), 'yyyy-MM-dd')

Monday

formatDateTime(subtractFromTime(addDays(utcNow(), 1), dayOfWeek(utcNow()), 'Day'), 'yyyy-MM-dd')

Expressions Breakdown

Now that you have the expressions, let me explain how they work. The first thing to know about expressions is that they work like Math: from the inside out. All the expressions functions are executed from inner to outer. In the case of the expression to get Sunday as the first day of the week, the expression works as follows:

  • The first function to be executed is the utcNow(). This will give us the current date and time in UTC format.
  • The next function to be executed is the dayOfWeek(). This will give us the current day of the week from the value of the utcNow().
  • The next function to be executed is the subtractFromTime(). This will take the date value of the second utcNow() function. Then, it will subtract the number returned by the dayOfWeek() function, which is the interval to subtract, and then it uses the Day time unit to know what to subtract.
  • The next and final function to be executed is the formatDateTime(). This will give the output of the subtractFromTime() function formatted as yyyy-MM-dd.

The only difference between the Sunday and the Monday expressions is that for Monday, we have to add another function, addDays(), to add 1 day to the current date in utcNow().

I hope this post is very informative and please do not hesitate to reach out if you have any questions.

Thanks!

Photo by Jazmin Quaynor on Unsplash

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s