Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Outlook
    • Planner
    • Power Apps
    • SharePoint
    • Teams
  • Triggers
  • Ready to use solutions
    • Approval Process Template
    • Task Delegation App
    • The Ultimate Power Automate expressions cheat sheet
    • Power Automate HTTP requests to SharePoint cheat sheet
    • Power Automate HTTP requests to Graph API cheat sheet
  • ABOUT ME
  • Get help with your flow
  • POWER PLATFORM SERVICES
Menu

How to add only working days to a date using Power Automate

Posted on March 6, 2022April 5, 2022 by Tom

“Can I let Power Automate to add only working days to a date, without the need to manually select the end date?”


When you add days to a date in Power Automate, it’ll simply add the defined number of days. It doesn’t care about weekends, all days are equal. But that’s not true in your work life. You care about weekends. If there’s a task that should take 10 days of work, you understand it as 2 weeks, not a week and a half. As such, you want the calculated date to be 2 weeks from today, not exactly 10 days. And that’s what this post is about – how do you ignore the weekends and add only working days to a date?

Note: in this simplified version it’ll ignore national holidays.

Update: Paul didn’t like my solution and described a much better one that can skip also holidays, you can find it here.

Add full weeks to a date

The first step is to calculate how many full weeks you’re trying to add. One work week is 5 working days, therefore, you should divide the number of days by 5. Use the div(…) expression to divide the number of days by 5.

div(outputs('Compose'),5)

You’ll need also the mod(…) expression to check if there’re no days left after the division, that the remainder is 0.

mod(outputs('Compose'),5)

Take the result, and if it’s more than 0 weeks and there’s no remainder, remove the last week. The reason is that the last up to 5 days should be always processed differently. It doesn’t have to be a full week, it can be only the 5 days.

if(
and(greater(div(outputs('Compose'),5),0),equals(mod(outputs('Compose'),5),0)), 
    sub(div(outputs('Compose'),5),1), 
    div(outputs('Compose'),5)
)

Multiply the result by 7, and add it to the date. You just added all the full weeks, e.g. to today’s date.

addDays(utcNow(),mul(outputs('Compose_2'),7))

That was the easy part, now you have to add the remaining days.

Add the remaining days to a date

Adding the days is a bit more complex as there’re many more options. It’s less than a week, and it can be any number between 1 and 5 days to add. It can end on a weekend, or it can go over a weekend…

Start by calculating how many days it should be. That means using again the remainder of the division by 5.

mod(outputs('Compose'),5)

If the remainder is 0, add 5 days (the days you subtracted before), otherwise use the remainder.

if(
    equals(mod(outputs('Compose'),5),0),
    5,
    mod(outputs('Compose'),5)
)

Add the number of days to the date with all the full weeks already added.

addDays(
    outputs('Compose_3'),
    if(
        equals(mod(outputs('Compose'),5),0),
        5,
        mod(outputs('Compose'),5)
    )
)

The last step is to check the day of the final date with the dayOfWeek(…) expression. Firstly, the date shouldn’t be on the weekend. If the date is on Saturday or Sunday, you must add 2 extra days to skip it.

if(
    or(
        equals(dayOfWeek(outputs('Compose_4')),6),
        equals(dayOfWeek(outputs('Compose_4')),0)
    ),
    addDays(outputs('Compose_4'),2),
    outputs('Compose_4')
)

Secondly, you must also check if there’s one more weekend to skip. Take the day number of the date after adding all the necessary weeks.

dayOfWeek(outputs('Compose_3'))

Take also the number of the second day, after you added the remaining days.

dayOfWeek(outputs('Compose_4'))

Now compare them. If the number of the second day is less than the first day, it went over the weekend and you must add 2 days. Combining it with the previous expression:

if(
    or(
        equals(dayOfWeek(outputs('Compose_4')),6),
        equals(dayOfWeek(outputs('Compose_4')),0)
    ),
    addDays(outputs('Compose_4'),2),
    if(
less(dayOfWeek(outputs('Compose_4')),dayOfWeek(outputs('Compose_3'))),
        addDays(outputs('Compose_4'),2),
        outputs('Compose_4')
    )
)
Power Automate add working days

Summary

As you can see in the solution above, you can make Power Automate add only working days to a date. The process could be split into 3 parts. Firstly, add the full weeks to the date. Secondly, add the remaining days to the date. And thirdly, make sure that the date doesn’t end on weekend and that you skip the last potential weekend.

There’re two moving parts in the solution – the date and the number of days to add. In this example I used today’s date and a compose action with the number of days, but you can of course load this data from somewhere else, e.g. SharePoint list.


πŸš€ Master Power Automate

Join 2,000+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

11 thoughts on “How to add only working days to a date using Power Automate”

  1. Josia says:
    March 7, 2022 at 1:35 pm

    Hi,

    thanks for this. This works great! How would you take into account national holidays? Is there a blogpost from yours on that topic? Maybe a topic for a future post πŸ˜‰

    Reply
    1. Tom says:
      March 9, 2022 at 8:10 pm

      Hello Josia,
      I don’t know how to include national holidays at this moment, I might take a look into it in the near future. πŸ™‚

      Reply
    2. Tom says:
      April 5, 2022 at 7:47 pm

      Hello Josia,
      take a look on this article from Paulie, his solution is much better and can handle also holidays: https://www.tachytelic.net/2022/04/power-automate-flow-add-working-days-date/

      Reply
  2. Yannie says:
    March 16, 2022 at 6:42 pm

    Hi Tom. Thanks – this is awesome! Could you tell me how I can base the a start date on a date column on my list rather than today’s date? I tried replacing the utcNow() in Compose 3 but it didn’t take it. Thanks again!

    Reply
    1. Yannie says:
      March 18, 2022 at 8:29 pm

      Hi. Please disregard my question above. It actually worked. Thanks.
      Yannie

      Reply
  3. Pingback: Add working days to a date in a Power Automate Flow
  4. Fatima says:
    June 14, 2022 at 2:19 pm

    how to calculate working exclude weekend start date and end date and -5 to 5 days add and subtract kindly tell me only count working days not weekend. For example today is tuesday and calculate next 5 working days Wednesday,Thursday,Friday and skip the saturday sunday and add momday and tuesday total 5 days count exclude weekends. Thank You.

    Reply
    1. Tom says:
      June 19, 2022 at 10:12 am

      Hello Fatima,
      Paulie has much better article on adding working days: https://www.tachytelic.net/2022/04/power-automate-flow-add-working-days-date/

      Reply
  5. Simon says:
    November 4, 2022 at 6:04 pm

    Well Done – Fantastic work. Good solve and learnt new tricks

    Reply
  6. Dmytro says:
    May 2, 2023 at 10:58 pm

    Thanks,

    I have extended your flow and added the opportunity to subtract days. Packed the child flow to the solution so the community can reuse it.

    https://github.com/LessCodeHub/PowerAppsSolutions/tree/main/Add%20days%20excluding%20weekends%20and%20holidays.

    Reply
  7. Baz says:
    August 23, 2024 at 3:15 pm

    Thanks for your site and solutions
    I like this solution, but I wanted the number of days being taken from the list.

    And if I do a Compose with
    {
    “inputs”: “@items(‘Apply_to_each’)?[‘Numb_x002e_Days’]”
    }

    then I get this error :
    Unable to process template language expressions in action ‘Compose_2’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘div’ expects its first parameter to be an integer or a decimal number. The provided value is of type ‘Array’. Please see https://aka.ms/logicexpressions#mul for usage details.’.

    Any idea ?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

πŸš€ Master Power Automate

Join 2,000+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

Working on an Approval process?

Use the Approval Process Template and the Task Delegation App to skip the hard part and deploy a fully functional approval solution on a SharePoint list in minutes! And then the next one, and the next one...

Approval Template Preview ✨ Learn more ✨

Turn ideas into flows with ease!

Grab the complete Power Automate Cheat Sheet Bundleβ€”everything you need to master expressions, SharePoint HTTP calls, and Graph API in Power Automate.

Cheat Sheet Bundle Preview ✨ Get the Cheat Sheets

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with thousands of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2025 Let's POWER Automate | Theme by SuperbThemes