Calculate Working Days in a time period using Power Automate

I’ve been working on a Flow recently that requires either the total amount of days between two dates, or the amount of working days (i.e. total days minus weekends and holidays), and the lack of a proper DateDiff expression in Power Automate has been a bit of a headache for me to work around.

If you’ve ever worked with Excel then you’ll know that the networkdays formula would be perfect for this scenario, so I wanted to see if I could replicate this in a Flow.

The Setup

The networkdays formula takes three parameters:

  1. Start Date
  2. End Date
  3. Holidays (an array containing a list of holiday dates)

We need to include the same parameters in our Flow, so you’ll need to ensure that your trigger contains this information, or that you can gather it within your Flow.

The Solution

The Flow that I created is below, and I’ll go through each step in turn to explain what I’ve done

1. Trigger – For this Flow I’m using a manual trigger that takes the three parameters I outlined above. Of course you could also get this to work from a CDS record or any other trigger. The Holidays are required to be input in a “yyyy-MM-dd” format, separated by a comma. I’ve also experimented with retrieving the holidays from the Gov.UK Bank Holidays API, the important thing is to be able to construct an array of Holiday days.

2. Initialize HolidaysArray – The first action is to take the comma-separated list of Holidays from the trigger and convert it to an array so we can use it later in the Flow. We do this using the Split expression; this will convert a comma-separated list such as 2019-12-25,2019-12-26,2020-01-01,2020-01-02,2020-04-10 into an array that looks like:

[
"2019-12-25",
"2019-12-26",
"2020-01-01",
"2020-01-02",
"2020-04-10"
]

3. Compose StartDateTicks – in Excel dates are stored as sequential serial numbers starting with 1 for January 1 1900. Unfortunately, Power Automate doesn’t do the same thing, so we need to be slightly more creative in order to calculate the number of whole days between two dates; the ticks function gives the nanosecond interval for a specified datetime. For example, the date 10 December 2019 has a tick value of 637115328000000000. We use the compose action to convert the input start date to it’s representative tick value

4. Compose EndDateTicks – as above, use another Compose action to convert the input end date to it’s representative tick value

5. Initialize FullDays integer Variable – Now that we have the tick values for the start date and the end date, we can subtract the start date from the end date then divide the result 864000000000 to convert the result to the number of days. We also add 1 to this result to give us the total number of whole days between the start date and end date, inclusive of the end date. The expression we use is

add(div(sub(outputs('EndDateTicks'),outputs('StartDateTicks')),864000000000),1)

Note: The next 7 steps in the Flow (Step 6 – Step 12) will help us to calculate the number of working days in the total number of days we had returned above.

6. Initialize WorkDays Integer Variable – now that we have the Full Days calculated, we’ll initialize another Integer Variable to calculate the Working Days. When we initialize it we’ll set the default value to the FullDays value and we’ll decrement it in subsequent steps

7. Decrement WorkDays by 2 for each Full Week – we know that in any given 7 day period there will be two weekend days, so we need to remove these from the total number of days returned above. The way we do this is to divide the total number of days value by 7, then multiply it by 2, and then subtract this result from the total number of days.

For example, if you had 14 days total, then you could divide this by 7 (giving 2), then multiply it by 2 (giving 4), and subtract this from 14 (giving 10). This tells you that in a 14 day period there are 10 working days and 4 weekend days. The expression we use to calculate this is:

mul(div(variables('Days'),7),2))

8. Decrement WorkDays by 1 if Start and End are the same day on a weekend – we need to account for situations in which the Start Day and End Day are the same day of the week (i.e. it starts and ends on Sunday). If it is midweek then we don’t need to do anything, but if it’s a weekend then we need to ensure we’re decrementing the WorkDays value by 1. The reason for this is that if we started and finished on a Sunday, this would be 8 days in total. The calculation at step 7 would remove 2 days for each full week, but we’d also need to ensure we’re removing an additional 1 day to account for the weekend.

if(equals(dayofweek(triggerBody()['date']),dayofweek(triggerBody()['date_1'])),if(or(equals(dayofweek(triggerBody()['date']),0),equals(dayofweek(triggerBody()['date']),6)),1,0),0)

9. Decrement WorkDays by 2 if Start Sat & End Midweek – in this step we’re checking if the time period we’ve selected for the Flow starts on a Saturday and ends on a midweek day. If so, we want to decrement the WorkDays variable by 2

if(and(equals(dayofweek(triggerBody()['date']),6),not(or(equals(dayofweek(triggerBody()['date_1']),0),equals(dayofweek(triggerBody()['date_1']),6)))),2,0)

10. Decrement WorkDays by 1 if Start Sun & End Midweek – this step is almost the exact same as above, but this time we’re checking if the time period starts on a Sunday and ends on a midweek day. If so, we want to Decrement the WorkDays variable by 1

if(and(equals(dayofweek(triggerBody()['date']),0),not(or(equals(dayofweek(triggerBody()['date_1']),0),equals(dayofweek(triggerBody()['date_1']),6)))),1,0)

11. Decrement WorkDays by 1 if Start Midweek & End Sat – this action is the inverse of Step 9; we’re checking to see if the time period selected starts on a midweek day and ends on a Saturday. If so, we want to Decrement the WorkDays variable by 1

if(and(not(or(equals(dayofweek(triggerBody()['date']),0),equals(dayofweek(triggerBody()['date']),6))),equals(dayofweek(triggerBody()['date_1']),6)),1,0)

12. Decrement WorkDays by 2 if Start Midweek & End Sun – as above, this is the inverse of Step 10; we’re checking to see if the time period selected starts on a midweek day and ends on a Sunday. If so, we want to Decrement the WorkDays variable by 2

if(and(not(or(equals(dayofweek(triggerBody()['date']),0),equals(dayofweek(triggerBody()['date']),6))),equals(dayofweek(triggerBody()['date_1']),0)),2,0)

NOTE: it would probably make more sense to have the decrement actions above contained within a Switch action to make your Flow more efficient

13. Initialize HolidaystoRemove – for this step we’re initializing another integer variable that we’ll use in the Condition in Step 14 to count the number of Holidays that occur in the selected time period

14. Check if the Date Range contains any of the holidays – for this step we’re going to iterate through the Holidays array we created in step 2 and check if that date is in between the Start Date and End Date of the time period we selected, using an “is greater than or equal to” and “is less than or equal to” condition respectively. If the holiday is in the time period, we’ll increment the HolidaystoRemove variable by 1.

15. Decrement WorkDays by HolidaystoRemove – Once we’ve iterated through all the holidays, we then Decement the WorkDays variable by the HolidaystoRemove variable, and this will give us our final amount of Working Days in our selected Time Period

16. Response – the final step in my Flow is a Response action. I’m going to be calling this Flow as a child flow from another one, so I need the response to return the information. In my response I have 3 outputs:

  1. Total Days
  2. Working Days
  3. Holidays Removed

Conclusions

This Flow was a bit more frustrating than I’d expected to try and pull together, and it involves some hefty expressions, but I’ve tested it pretty thoroughly and its worked in all the scenarios I’ve thrown at it, but I’d love for other people to do more testing.

I would love to get your feedback on whether you think this is useful, or if you think I’ve missed anything or made any mistakes!

If you’d like to download a copy of this Flow please click here

Creating a Lead for every ClickDimensions Posted Form using Power Automate

Recently, my good friend Megan Walker did a guest post for ClickDimensions showing how to create a Lead for every ClickDimensions Posted Form and if you haven’t read it then you really should!

When I was reading this post one of the things I noticed was the requirement to use a Filter Array step and Compose step for each question in your Form in order to be able to use them when you created the Lead. This approach works perfectly, but it could be quite time consuming to create if your Form has lots of questions, so I wondered if there could be a way to simplify it a little bit.

Please note, for the purposes of this post I’m only going to be focusing on an alternative way to get the Posted Field data and use it to create your Lead, Megan has covered everything else in her post so please read it!

If you’d prefer to watch a video overview of this blog, click here.

The Solution

The first step is still to List your Posted Fields using the List Records action. If you’re using the Common Data Service (Current Environment) connector then you’ll be able to select the specific attributes you want returned. In this case we’re only interested in the Label (i.e. the Question) and the Value (i.e. the Answer). You should also set the Order By to the Label; this will ensure consistency in the returned results which is important for later steps in the Flow.

If you look at the Output for the List Records action, you’ll see that it gives a JSON array that looks something like:

We want to make matching pairs for the Questions (Labels) and Answers (Values) from each of the returned Posted Fields and combine them in an array. Fortunately for us, in Power Automate there is an Action called Select that allows you to “Select the specified properties from all elements of the ‘From’ array into a new array”. This is really just a fancy way of saying you can take the elements of the output above that you like and keep them, while disregarding the rest, and you can reshape them into pairs as required.

For the Select action, we take the data From the output of the List Records step, and then we create a map of the Label and the Value as a name pair

When this is done, the output from the Select action will look something like this:

This is much better!

As you can see, the Questions and Answers have been combined into array elements. You can also see that, as we set the Order By to the Label in the List Records Step above, the array elements are listed alphabetically. One important thing to note at this stage is that each element in an Array has an index number beginning from 0, so you can identify the array elements I’ve returned above as follows

The final step for creating the Lead is to add a Create a New Record action, and then we’re going to use an expression to pull the values from the output above into the fields we need to populate:

As you can see, for each field we are populating we have an expression with the following format:

@{outputs('Select')['body']?[0]?['Company Name']}

The key things to note in the construction of this expression are:

  1. for the Outputs expression, the name ‘Select’ must match the name that you’ve given to the Select step
  2. the Integer number [0] must match the element number for the array element as indicated above. For example, if we want to set the Telephone Number we will use [4]
  3. The final part of the expression [‘Company Name’] must match the text of the Question from the array.

If we wanted to get the Email Address instead of the Company Name then the expression would be:

@{outputs('Select')['body']?[5]?['Work Email Address']}

After we’ve completed all of the above our flow now only has three actions to retrieve Posted Fields and to create a Lead with the answers submitted:


Conclusions

The first thing to say is that none of this would have been possible without the valuable insights of Megan Walker and Rob Dawson. I keep saying this, but one of my favourite things about this community is the collaborative efforts we all make to help each other.

The second thing is that while this works, if the Questions on your form were to change, either by adding/removing questions or by changing the Label of the questions then this would probably affect the success of the Flow, so it might be worth putting some additional validation logic in to catch any potential issues like that.

The final thing is that Power Automate is so powerful and it’s amazing what you can achieve with some lateral thinking. This has been a fun little challenge for me, and hopefully you find it useful. Please reach out to me if you have any questions or comments.

Using Flic Buttons with CDS – Dynamically setting Option Set fields

In my previous post I demonstrated how to use a Flic button with CDS to create a Fire Evacuation record and notify Users that an office is being evacuated. The eagle-eyed amongst you may have noticed that the Office option set field value was hard-coded, meaning no matter where you were when you triggered the Flic button it would create an evacuation record against the specifically named office, so you’d have to have a Flic button for each office in your organisation, which might be a challenge if you regularly visit multiple offices in your organisation.

In this blog I’m going to show you how we can use the location data from the Flic buttons and dynamically set the option set field for the office location.

The Scenario

In this scenario, my organisation has 4 offices spread throughout the UK:

  • Glasgow
  • Edinburgh
  • Birmingham
  • London

I may be required to visit any of these offices throughout the course of my working week, and to reduce confusion for me I’d like to ensure I only have to carry one Flic button that I can use to trigger the process to record an Evacuation and to notify others within my organisation, and have the Flow recognise which Office I am in and ensure the record is populated accordingly. Furthermore, if I am too far from any of the offices when I click the button, I’d like the Flow to notify me of this if I accidentally try to trigger the process to record the evacuations.

The Setup

For this flow we’ll be using the same Evacuation entity I configured in the previous blog. I’m also going to be using a Bing Maps – Get Route action, so you need to ensure you’ve signed up for a Bing Maps API key. The final preparation step is to get the Office Option Set values (as highlighted below) for use in the Flow.

The Solution

This Flow is a bit longer than the previous one, so I’ve included an image below of the whole flow, and I’ll go through each step in turn to explain what I’m using them for.

1. Trigger – Same as in the previous blog, this is triggered using a “Click” event from a Flic button

2. Compose – as per the previous blog, this is an expression to take the DateTime value from the Click Time value and format it to make it more “friendly”, using the expression below

@{formatDateTime(triggerBody()?['clicked_at'], 'dd MMMM yyyy')}

3. Initialize Offices Array – this is just an Array variable we’re going to use later in the Flow

4. Compose Office Info – In this Compose action I’m inputting an array that includes the Office location, the Office address, and the Option Set Value that corresponds to the office

5. Parse Office Info – I’m using a Parse JSON action to parse the array I created in the previous step, using the following schema

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Office": {
                "type": "string"
            },
            "Address": {
                "type": "string"
            },
            "OptionSetValue": {
                "type": "integer"
            }
        },
        "required": [
            "Office",
            "Address",
            "OptionSetValue"
        ]
    }
}

6. For Each Office loop – For each office from the array we created in Step 4, we carry out the following actions

6A. Get Distance to Office – For this action we’re using the Bing Maps Get Route action to calculate the distance from where we were when we clicked the Flic button (using the Latitude and Longitude that are included as parameters from the Flic trigger) to the Office (using the address we put into the array in step 4)

6B. Compose Office Distance – for this step we’re going to take the Distance output from Step 6A, and the Office name and Option Set value that we parsed from the array we created in Step 4 and put them into a new array element

6C. Append to Offices Array – we take the output from the Compose Office Distance step above and append it to the Offices Array we created in Step3

7. Initialize SuccessfulActions – we initialize a new integer variable called SuccessfulActions that we’ll use in Step 9 and Step 10 below, ensuring the default value is set to 0

8. Parse Offices – next we use another Parse JSON action to parse the Offices Array variable that we’ve been populating in step 6, using the schema below:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Office": {
                "type": "string"
            },
            "Distance": {
                "type": "number"
            },
            "OptionSetValue": {
                "type": "integer"
            }
        },
        "required": [
            "Office",
            "OptionSetValue",
            "Distance"
        ]
    }
}

9. For Each Office Distance loop – in this loop we’re going to be checking if the Flic has been triggered within a set distance of any of the offices, and if so we’ll be creating a new evacuation record

9A. Check if the Flic has been triggered close to the Office – we use a Condition action to check if the Distance from the Office is less than a set amount. In my example I’ve set it to check that the distance is less than 1 mile, but you can set accordingly

9B. Create an Evacuation Record – if the condition above is satisfied then we use a CDS Create Record action to create a new Evacuation record, and we set the Title using the Office value from the Offices Array and the formatted DateTime from Step 2. We then set the Office Option Set using the OptionSetValue from the array, and set the Start Time using the Click Time from the Flic Trigger

9B. Increment Successful Actions – We then increment the SuccessfulActions variable we initialised in Step 7 by 1, as this will be used in Step 10 below

10. Check if the Flic has been triggered too far from any office – we use a Condition action to check if the SuccessfulActions variable equal 0. If so, we send a mobile notification to the triggering user to to let them know they’re too far from the office to trigger an evacuation record

Conclusions

This Flow hopefully demonstrates a viable way to use array variables to dynamically set fields on CDS records; I’ve had particular issues with Option Set fields in the past so I’m happy to have found a potential solution to this problem. Ideally I’d be able to retrieve the Option Set values dynamically, but I’m not aware of any way to retrieve them from CDS. If you are reading this and know of a better way please let me know!

If you’d prefer to watch how to do this, check out the video by clicking here