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

6 thoughts on “How-To #3: Get First Day of the Week using Power Automate”

    1. Hello,
      To get the last Thursday of the month, there is a 2-step approach involved, the first one being getting the last day of the month. This is how you would do it:
      1. Add a Compose action and rename it to Last Day of Current Month.
      1.1. Add the following expression to this action:
      addDays(startOfMonth(getFutureTime(1, ‘Month’)),-1, ‘MM-dd-yyyy’)
      2. Add another Compose action and rename it to Last Thursday of Current Month.
      2.1 Add the following expression to this action:
      formatDateTime(subtractFromTime(addDays(outputs(‘Last_Day_of_Current_Month’), 4), dayOfWeek(outputs(‘Last_Day_of_Current_Month’)), ‘Day’), ‘yyyy-MM-dd’)
      3. Save the flow and test it.

      Now, this is how the expressions work:
      1 – addDays(startOfMonth(getFutureTime(1, ‘Month’)),-1, ‘MM-dd-yyyy’)
      This expression first executes the getFutureTime() function, what it does is gets the value of next month. Then, the startOfMonth()
      function gets the first day of the month retrieved in the getFutureTime() function. Lastly, the addDays() function subtracts 1 day from
      the output of the startOfMonth() and the getFutureTime() functions and formats the date output as MM-dd-yyyy.

      2 – formatDateTime(subtractFromTime(addDays(outputs(‘Last_Day_of_Current_Month’), 4), dayOfWeek(outputs(‘Last_Day_of_Current_Month’)), ‘Day’), ‘yyyy-MM-dd’)
      This expressions first executes the dayOfWeek() function and the output it gives is an integer of the day of the week from the output
      of the Last Day of Current Month action. The next function is the addDays(), and this adds 4 days to the output
      of the Last Day of Current Month action. Once these functions are complete, the next function is the subtractFromTime() function,
      and this one subtracts the output of the dayOfWeek() function from the output of the addDays() function. Lastly, the formatDateTime() function formats the date output as MM-dd-yyyy.

      Please try this and let me know. Thank you!!!

      Like

  3. Hi Fausto,

    I’ve got a problem with a flow in Power Automate. Can you hel me please? I created a flow… when a new e-mail arriving in a shared e-mail box, this flow creates a new task in Planner. So… at the moment it also updates the task information by adding the due date, taking the message arrival date and adding 2 days.

    The actual function is: addDays(triggerOutputs()?[‘body/receivedDateTime’], 2)

    But, I would like the function not to calculate the days of the weekend (Friday and Saturday) in this delay. Is it possible? I saw the function dayOfWeek and I tried to insert it. But I can’t uderstand how cobine it with the function addDays.

    Thank you very much. Gimmy.

    Like

Leave a comment