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. 😎
formatDateTime(subtractFromTime(utcNow(), dayOfWeek(utcNow()), 'Day'), 'yyyy-MM-dd')
formatDateTime(subtractFromTime(addDays(utcNow(), 1), dayOfWeek(utcNow()), 'Day'), 'yyyy-MM-dd')
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.
I hope this post is very informative and please do not hesitate to reach out if you have any questions.