Leverage Outlook Calendar & Select/Filter Actions

MarlaynaJack · ‎06-02-2022

Hello,

I was hoping I could figure this one out on my own but I'm just not having any luck. Here is what I have:

Context: The group I work with gets assigned several projects weekly. I am trying to create a tracker that calculates the due dates for new projects based on a few pieces of data. First, the default due date is 15 business days from the start date. However, sometime the team that assigns these projects fails to send us some documentation, and this changes the due date. I also need the solution to recognize weekends and holidays.

I have a list of all of the company holidays created.

My task tracking list has the following relevant columns:

Item Name -- Assignment Date (i.e the default start date. Date field)--Was there a delay (check box- I can change to drop down if that helps)--Start date due to delay (optional- date field)-Business Days Allowed (Defaults to 15)--Due date (date field)

I am trying to create a flow that can determine whether or not the "Was there a delay" box is checked. If it not, then it takes the initial start date, checks the holiday list, and then calculates the due date and automatically enters it in the due date field. If the box is checked, it does the same thing, except it uses the start date due to delay field as the starting point. I found a solution that should at least do part of this (calculating due date excluding weekends and holidays) but whenever I try to use it, I get the answer 44,743 as the result in the due date field. 😅 Can someone please help?

eliotcole · ‎06-02-2022

Hi, @MarlaynaJack, this was actually asked quite recently, and has a few solutions dotting around this fine little community ... some of which I'm sure might even be in that 'Recommendations' box there on the right of our conversation. 🙂

Here's a blog that was recently referenced:

https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Calculating-working-days-in-Power-...

~~I'll see if I can edit this response with the one I'm remembering, but have a bit of a root around in the meantime!~~

I could not remember it, but if you search on weekend holiday (or weekends holidays) then you're bound to find a solution.

Myself? I try to keep it as simple as possible by maintaining a nationalHolidays calendar in Outlook, and getting the events that fall within whatever period of time I'm checking. This one is pulling from my United Kingdom holidays calendar:

CALENDAR - Get events between two dates.jpg

Start/DateTime ge '2022-06-01T00:00:00.0000000' and End/DateTime le '2022-06-20T00:00:00.0000000'

This way I can either maintain the holidays calendar externally if needs be.

The dates in that Filter Query can be replaced with values from your flow relatively easily enough.

OK, I made a simple flow that leverages the above Calendar of holidays, and a quickly made check of any SAT/SUN amounts.

Leverage Outlook Calendar & Select/Filter Actions

Prep - Create The Calendar

This is simples, just open Outlook 365 online, tap the calendar icon, and add a calendar. Select 'Holidays' on the left, and just add the country(ies) you need holidays for and it will add a calendar for each one.

The Flow

Here's the flow in full, I'll detail a couple of the steps afterwards:

Here's the flow step by step:

Spoiler

1

numDaysToAddCNST

This always stores your default days added, in this case, 15.

2

Select

The From field of this Select action makes a range() from 0 to 15, meaning it creates an array, where each item is purely a consecutive integer from 0 to 14.

The Map field creates a map by using the addDay() function to add the current item()'s number to the date the ticket was created. Then it formats that date into 'ddd' (Wed/Thu/Fri) format, then it makes that upper case.

From	`range( 0, outputs('numDaysToAddCNST') )`
Map	`toUpper( addDays( triggerOutputs()?['body/Created'], item(), 'ddd' ) )`

3

Filter

This filters all of the results of that Select action to purely those which are SAT or SUN.

You may need to build the or() function here, the easiest way to do this is to create each equals function using the basic edit option, switch to advanced and cut it out. Then just place a comma between them, and brackets at either end with 'and' at the start and Bob's your Uncle.

@or(
    equals(
        item(), 
        'SAT'
    ), 
    equals(
        item(), 
        'SUN'
    )
)

4

numberOfWeekendsCNST

This is a simple count of how many items that the Filter array action ends up making.

length(
    body('Filter_array')
)

5

Add to time for initialDueDate

This is a Date Time action which simply adds a number of days to the first date specified. In the Interval field I simply used an add() function in an expression to get the total amount of days to add.

add(
    outputs('numDaysToAddCNST'),
    outputs('numberOfWeekendsCNST')
)

6

Get events (V4)

This I've covered above so I won't go into it, however you can see that I've included the 'Created' date from the trigger, and the newly made date from Add to time for initialDueDate ...

7

numberOfHolidaysCNST

This simply uses length() to count the amount of events that the Get events (V4) finds previously.

length(
    outputs('Get_events_(V4)')?['body/value']
)

8

Add to time for finalDueDate

Here the Add to time action uses the 'Created' date from the trigger, and the numberOfHolidaysCNST in the previous step.

9

Update item

Finally I can update my list item with the due date, and use some of the data made here to fill some other fields.

1 numDaysToAddCNST This always stores your default days added, in this case, 15. 2 Select The From field of this Select action makes a range() from 0 to 15, meaning it creates an array, where each item is purely a consecutive integer from 0 to 14. The Map field creates a map by using the addDay() function to add the current item()'s number to the date the ticket was created. Then it formats that date into 'ddd' (Wed/Thu/Fri) format, then it makes that upper case. From range( 0, outputs('numDaysToAddCNST') ) Map toUpper( addDays( triggerOutputs()?['body/Created'], item(), 'ddd' ) ) 3 Filter This filters all of the results of that Select action to purely those which are SAT or SUN. You may need to build the or() function here, the easiest way to do this is to create each equals function using the basic edit option, switch to advanced and cut it out. Then just place a comma between them, and brackets at either end with 'and' at the start and Bob's your Uncle. @or( equals( item(), 'SAT' ), equals( item(), 'SUN' ) ) 4 numberOfWeekendsCNST This is a simple count of how many items that the Filter array action ends up making. length( body('Filter_array') ) 5 Add to time for initialDueDate This is a Date Time action which simply adds a number of days to the first date specified. In the Interval field I simply used an add() function in an expression to get the total amount of days to add. add( outputs('numDaysToAddCNST'), outputs('numberOfWeekendsCNST') ) 6 Get events (V4) This I've covered above so I won't go into it, however you can see that I've included the 'Created' date from the trigger, and the newly made date from Add to time for initialDueDate ... 7 numberOfHolidaysCNST This simply uses length() to count the amount of events that the Get events (V4) finds previously. length( outputs('Get_events_(V4)')?['body/value'] ) 8 Add to time for finalDueDate Here the Add to time action uses the 'Created' date from the trigger, and the numberOfHolidaysCNST in the previous step. 9 Update item Finally I can update my list item with the due date, and use some of the data made here to fill some other fields.