web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / calculate business day...
Power Automate
Unanswered

calculate business days in workflow, exclude weekends and holidays

(0) ShareShare
ReportReport
Posted on by 3

Hi!

 

I'm creating a workflow of reminders but I'm kinda stuck at the moment. 

 

I have created this list:

listSP.png

 

and until now I'm testing by triggering the workflow manually but it's going to be a scheduled workflow. The workflow consists of sending a reminder 3 days in advance, 1 day in advance and on the same day as the date entered in the field (Compliance date and / or report). Until there, everything is fine!:

listSP.png

What has been difficult for me is how to calculate what the working days are, in this case Monday through Friday. I need to exclude Saturdays, Sundays and holidays.

 

¿What should i do to make it work?

Could you please help me! and I really appreciate it.

Categories:
I have the same question (0)
  • haroldk Profile Picture
    Microsoft Employee on at

    I have worked on a flow that will calculate the end date given the start date and the number of working days. This formula will exclude weekends. You might be able to use this to get the working days. Another option would be to use an unattended UI Flow and run a calculation in Excel to return the number of working days.

    https://docs.microsoft.com/en-us/power-automate/ui-flows/overview

     

    haroldbk_msft_0-1598305681715.png

    This is the expression in Compose 2 - Compose 3 outputs the end date.

     

     

    if(equals(dayOfWeek(adddays(if(equals(dayOfWeek(variables('startdate')),6),addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))),6), addDays(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5))),2), if(equals(dayOfWeek(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))),0), adddays(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5))),1), adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))))

  • IsaacM22 Profile Picture
    3 on at

    Thank you very much for your answer.

    Until now I have had no success to exclude weekends at least.

     

    I've done this:

     

    SP.png

     

    In the "compose" block, I have this expression:  

    dayOfWeek(utcNow()) and returns the day of the week. in the condition valid that.
     
    the flow begins by sending a reminder email, for example. (An email must be sent 4 days in advance) then, if the date is for ("09-01-2020" Tuesday). then the reminder email should be sent today ("08-26-2020" Wednesday). Counting business days (Thursday, Friday, Monday and Tuesday). I can't validate that, it doesn't exclude Saturday and Sunday and by not excluding the weekend, the mail is sent for Sunday, which cannot be the case.
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
11manish Profile Picture

11manish 227

#2
David_MA Profile Picture

David_MA 204 Super User 2026 Season 1

#3
Valantis Profile Picture

Valantis 177

Last 30 days Overall leaderboard