• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

Add working days to a date in a Power Automate Flow

April 5, 2022 by Paulie 6 Comments

In this post I will show you a Power Automate Flow you can use to add days to a given date and exclude weekends, or specific dates from the result. I was inspired to do my own solution by some recent posts by others:

Tom Riha – How to add only working days to a date using Power Automate
Matthew Devaney – Power Apps Calculate Business Days Excluding Weekends & Holidays

Both of these guys have brilliant blogs and I highly recommend subscribing to both of them. It is really interesting to see how we all approached the problem slightly differently.

Power Automate includes the useful expression addDays which will add a specified number of days to a date that you provide. While this function is useful, it does not allow you to exclude certain days. Such as weekends or public holidays.

Basic Flow – Add days excluding weekends

First, add two compose actions, name the first one Date and the second one DaysToAdd.

Power Automate Compose Actions to setup the date additions.

Next we will generate a range of dates for the next 365 days and also what day of the week they represent. Add a Select action and name it Dates:

The expressions used in the select:

From: range(0,365)
Date: formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd')
Day: dayOfWeek(addDays(outputs('Date'),item()))

A sample of the output of the select action will be a range of 365 dates, something like this:

[
  {
    "Date": "2022-04-04",
    "Day": 1
  },
  {
    "Date": "2022-04-05",
    "Day": 2
  },
  {
    "Date": "2022-04-06",
    "Day": 3
  },
  {
    "Date": "2022-04-07",
    "Day": 4
  },
  {
    "Date": "2022-04-08",
    "Day": 5
  },
  {
    "Date": "2022-04-09",
    "Day": 6
  }
]

As you can see, we are generating an array of dates, and the day of the week that date represents (0 being Sunday and 6 being Saturday).

Next, add a Filter Array step, to reduce the dates array so that it only includes week days:

The expression used in the Filter is:

@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))

Now we have a date array that contains only weekdays, we just need to pick out our target date. Add a compose action called TargetDate:

The expression used here is:

body('Filter_array')[outputs('DaysToAdd')]['date']

This expression will return just the date that we are looking to find, here is the output from my example:

The TargetDate compose action is optional, you can simply use the expression shown in any of your actions.

Complete Code for Basic Example

If you prefer, you can simply copy the code below into your clipboard and paste it into your own flow:

{
	"id": "df633c17-f74d-48fa-bd16-e67fbcc8eeef",
	"brandColor": "#8C3900",
	"connectionReferences": {},
	"connectorDisplayName": "Control",
	"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
	"isTrigger": false,
	"operationName": "Add_Days",
	"operationDefinition": {
		"type": "Scope",
		"actions": {
			"Date": {
				"type": "Compose",
				"inputs": "2022-04-04",
				"runAfter": {},
				"metadata": {
					"operationMetadataId": "f03622ae-0e01-4a52-957b-476f810aea4d"
				}
			},
			"DaysToAdd": {
				"type": "Compose",
				"inputs": 10,
				"runAfter": {
					"Date": ["Succeeded"]
				},
				"metadata": {
					"operationMetadataId": "22b2d012-3447-4ac0-80a4-24251d06ff99"
				}
			},
			"Dates": {
				"type": "Select",
				"inputs": {
					"from": "@range(0,365)",
					"select": {
						"Date": "@formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd')",
						"Day": "@dayOfWeek(addDays(outputs('Date'),item()))"
					}
				},
				"runAfter": {
					"DaysToAdd": ["Succeeded"]
				},
				"description": "range(0,365)",
				"metadata": {
					"operationMetadataId": "362d7f26-03cb-4a8c-a70e-8e75019a1061"
				}
			},
			"Filter_array": {
				"type": "Query",
				"inputs": {
					"from": "@body('Dates')",
					"where": "@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))"
				},
				"runAfter": {
					"Dates": ["Succeeded"]
				},
				"metadata": {
					"operationMetadataId": "bc17d8a4-b5b8-4a5b-bd3a-34fcd2d0f2ae"
				}
			},
			"TargetDate": {
				"type": "Compose",
				"inputs": "@body('Filter_array')[outputs('DaysToAdd')]['date']",
				"runAfter": {
					"Filter_array": ["Succeeded"]
				},
				"description": "body('Filter_array')[outputs('DaysToAdd')]['date']",
				"metadata": {
					"operationMetadataId": "19be198e-647c-40bf-9109-86261450a9ef"
				}
			}
		},
		"runAfter": {},
		"metadata": {
			"operationMetadataId": "ff58b7e7-a16e-4a1c-9ffe-b377e6b228ae"
		}
	}
}

Add Filtering of Public Holidays

It is possible to enhance the flow above so that it will also exclude public holidays in addition to the weekends.

First, setup a SharePoint list that contains all of the dates you want to skip. I setup my SharePoint list with two columns. Title and HolidayDay. This is how my list looks:

SharePoint list showing public holidays which will be excluded from the result.

Next we can modify our flow to also exclude these dates. We need to add an additional three actions:

First, add a Get Items (SharePoint) action right below the existing Filter Array action and point it at your site and your Holiday Calendar list.

Then add a Select action, rename it to HolidayDays. Modify the select action so that it is in text mode by clicking the small grid icon on the right side of the Map field. Populate the From field with the value from the Get Items action. In the Map field choose the HolidayDay.

Next add a filter action and rename it to ExcludeHolidays. The from will be the body output of our original filter array. On the left side of the filter we choose the output from the HolidayDays action and choose the option does not contain on the filter. On the right hand side of the filter use the expression item()[‘Date’] – It is much easier to comprehend in a image:

Finally, modify the expression in the TargetDate compose action to:

body('ExcludeHolidays')[outputs('DaysToAdd')]['date']

Now your TargetDate output will exclude both weekends and any public holidays that you have specified.

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Comments

  1. Terri-Ann says

    April 5, 2022 at 5:19 pm

    Genius, i will try this in my flow. Thank you for sharing this.

  2. Frederic says

    April 6, 2022 at 6:13 am

    Brillant, as always. Eager to test this. Thanks Paulie!

  3. David Lee says

    April 8, 2022 at 8:14 am

    Hey Paul – excellent flow you have built here, so thank you. I’m using it to create a recurring task in Microsoft planner.

    One small issue – when I enter the starting date (input) as 2022-04-01 and am looking to find Business Day 15 (input), the output for TargetDate is 2022-04-27, one day later than it should be.

    I get the feeling this is like a teams date addition problem. Any suggestions? Thanks.

  4. Pamela says

    May 19, 2022 at 3:54 pm

    Dear Paul – Many thanks for this great example, it helped me a lot. Any idea how can I get the business day -1? For example, today (19.05.2022) I need the output to be yesterday’s date (18.05.202). Thank you in advance,

  5. Paulie says

    May 19, 2022 at 4:31 pm

    Hi Pamela,

    Just took a look at this for you. It can be done quite easily. Modify the two expressions in the select action.

    For the Date value:
    formatDateTime(addDays(outputs('Date'),mul(item(), -1)), 'yyyy-MM-dd')

    For the Day Value:
    dayOfWeek(addDays(outputs('Date'),mul(item(),-1)))

    The key here is to multiply the current item by -1. So this removes days instead of adding them by changing the number of days to add to a minus figure. Reach out to me via the get in touch form if you get stuck and I will help you out.

  6. Pamela says

    July 19, 2022 at 1:15 pm

    Many thanks, Paulie. It worked!

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee
Image link to all Power Automate content

Excellent Power Automate Blogs

  • Damien Bird
  • Dennis (Expiscornovus)
  • Tom Riha

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 270 other subscribers.

Go to mobile version