Calculating Working Days in Power Automate & Power Apps | #PowerAutomate #PowerApps #Tutorial

hi there folks in today's demonstration I'm going to show you how to calculate the number of working days between two dates by excluding weekends and also public holidays so I'm going to do this in both power automate and powerapps using the same logic in power automate I'm going to use my favorite action the select and also the filter array action combined with the range expression in powerapps I'm going to use the for all the filter expression and also the sequence expression so we'll achieve the same thing we'll combine these to create a table or an array of data we can filter upon that data exclude the dates that we don't need and then we'll be able to get account based on the number of those days so I go into both options in details so you can understand the logic but also how to apply it plenty to learn as usual so make sure that you watch on don't forget to like And subscribe and without further Ado let's jump into the demonstration so I'm going to click things off from Power automate and I have my trusty manual trigger and I'm going to add in a couple of date inputs for the start date and the end date on our appropriately name them now now the aim here is to detect the number of days between the start and the end date and then we'll compare against the list that I have that has the national holidays to exclude any of those dates and finally remove any Saturdays and Sundays and that will then allow us to determine the number of working days between the start date and the end date and you'll see as I build the second solution there are actually some similarities between both Solutions so next up I'm going to add in the get items action and I have a list I've already built it's on my create me a list site and there's a video from last week where I showed you how you can programmatically create a SharePoint list using power automate but I'm going to select my national holidays and if I put a forward slash in here I'll remove any warning messages that you might get as a result of using this get items action over on SharePoint I have my SharePoint list and I have two days in July which is where we are right now the 17th and the 21st so I'm going to look to request a period of leave which will include these two dates and hopefully they'll remove them as a result so take note the 17th and the 21st of July back over on power automate I want to turn this array of data from my SharePoint list into just an array of dates so we'll use that select action put ourselves into text mode and then I can select the date which is the date for my national holidays so this will give us a brand new array purely with the dates and it'll remove all the things like the item id Etc next up we want to calculate the number of days between the the two dates that we've been given and I'm going to add a compose action I'll call this date difference and then the expression we're going to use is based on the date difference expression and so what this expects is a date one and a date two to compare against or go into the dynamic content and if I scroll down hopefully we can see the start date and then the end date and that should give us not only the number of days but also the number of hours minutes and seconds but we will remove the hours and minutes and seconds because all we're interested in is the number of days so I'll hit OK and then finally I'm going to add in another compose which will not only use to split the date Different Strings so we can get the number of days back but we'll also add in a one because we think about the date difference between the 10th and the 13th 13 minus 10 is 3 albeit there are four days in that period the 10th the 11th the 12th the 13th so if we add on one we'll also get that full number of days between that date difference range and that's important for the next part of the solution so if I go into the expression tab I'm going to use the Expression split and what we're going to do is we are going to split the string from compose date difference if I insert a comma we're going to split it on a full stop so you'll see there I put a full stop if I put a question mark and then two square brackets I can put a zero that's going to return the first part of the split which will hopefully be the number of days I need to turn that into an integer so I need to use int so I've gone back to the beginning of the expression there using my my cursor if I go and type in int I can jump all the way to the end now and put a closing bracket so that's now converted that string into an integer and then finally if I go right back to the beginning again I want to add and I can put an opening bracket A1 onto that whole string that we've created on that integer so if I put a comma I can put in a one I could put in a closing bracket and that's now going to split the string from the compose date difference retrieve the number of days convert it into integer and then add on one so I'll say okay to that and I think it's now a good time just to test and demo where we're at before moving on to the next stage of the flow so when I run the flow you'll see that it prompts me for a date so I'll use the 10th to the 13th now we know that there's three days but I'm expecting to return four if I hit done it runs nice and quickly we can have a look at that select which is taking the full array from the get items it's turned into an array of dates based on that data that comes back from our holiday we're not actually using that at this point we then got the date difference which shows three and we also have the hours and minutes and seconds which is all zeros but we are going to perform a split based on that period that I've just highlighted and if I go into the compose you'll see that we've got got the result of four if we jump back into edit what I want to do next is to use a select to create an array or a table of data based on the dates in the date range to see whether or not they are a particular day of the week for instance a Saturday or Sunday but also determine if they are a public holiday so we'll rename this select to our date array and then the input is going to be based on an Expression so this expression is based on what's called a range which will create a number of numbers in an array starting from an number which is in this case going to be zero and then the number of numbers in that range so if I put in 4 I'll get the number 0 1 2 3. so what we're going to use here is the date difference that we've calculated which is in our compose above if I go into Dynamic content I can choose that compose hit OK and now based on the date difference if it's three days four days five days we'll get in the array of numbers from zero counting up to that number based on that date difference now this is important because we're going to use it in one of the calculations coming up in the next part of this solution in the map I thought it was useful to first of all output the date then I thought it'd be quite useful to have the day I is a Monday Tuesday Wednesday Thursday Etc and then finally is it a public holiday so I'm going to call that public hole now in terms of the Expressions what we're going to do here is we're going to use the Expression add days if I go into the enter value into Expressions type in add days we can simply use that date that we had as part of the input the beginning of our flow so we have the start date I'm going to add on the number based on the expression item and so item is based on each of the numbers in this range which we've said will start from zero so the first example is going to be the start date the second one is going to be the day after start date and so on all the way up to the number of days based on that date difference so hit OK there and then we need to go and calculate the actual day of the week is it a Monday Tuesday Wednesday Thursday so we're going to do exactly the same thing again we're going to use add days an open close bracket we'll go into the dynamic content we'll go and hunt down that start date put in a comma use that expression item again but there's actually a third parameter on ADD days which is the output format so in this case we're going to use single quotes and put in the string four D's and by putting in four of these that's going to get us the day of the week based on the string Monday Tuesday Wednesday if we put in 3DS it would be the shortened so mon Chu Etc so I hit OK to that and then finally how do we know if this is a public holiday or not well we're going to use an expression known as contains and that will allow us to check that array that we've created in the select with all our holidays does it contain a date from within this select array so we're going to use the add days expression again we're going to scroll down and find that start date I'll put in another comma we're going to put in item so that we're adding that 0 1 2 Etc based on that Loop and that range and then finally we just need to make sure that it's in the same format as the dates that's in the select above and I know that it's in the year year which is for wise the month which is capital M's not lowercase which is minutes and then the lowercase D for days and that'll allow us to make sure that that array is in the same format as the array that we have for our holidays so hit OK and now what we have is an array that shows us the date based on that range we have the day is that Monday Tuesday Wednesday Etc which we can then filter on to remove our Saturdays and Sundays or your Mondays and your Fridays whatever you have for your particular requirement and then we've got this public holiday lag true or false does the date that we have for that Loop is it contained in the select from our SharePoint list next up what we want to do is we want to filter that array because we want to remove the Saturdays The Sundays you might have a different requirement you might want to keep the Saturdays and Sundays and remove the Wednesdays or something else but we're going to take the output from our select date array and then we're going to perform an expression a number of Expressions we'll start with something basic we'll check to see if the day is not equal to Saturday so I'm going to go into the expression tab here type in item open close brackets with the square brackets and single quotes type in day because that's based on the key that I've supplied in the array above hit OK and we want to make sure that it's not equal to and we're going to choose the string Saturday so we want to remove all the Saturdays I also want to remove all the Sundays I also want to make sure that the public holiday a is not equal to true and in this situation with the filter array I cannot add more than one condition unless I go into advanced mode and by doing so we can now see our expression at the bottom here so I'm actually going to copy this and then I'm going to pop open a notepad and show you how we're going to build upon this existing expression so here we are in my trusty notepad if I paste in that expression here we're checking to make sure that the day is not equal to Saturday if I put in a return paste that again we also want to make sure that it's not equal to the Sunday and then finally we want to check to make sure that the public holiday so if I go to public holiday and type in public hole because again that's the name of the key within Ira is not equal to the Boolean true so I can just go and change this to True here it's not a string so it doesn't need to be in quotes now we want all of these to apply in which case we use the expression and and if I put and in just after that first at an opening bracket and if I go all the way to the end of this expression here and put in a comma I can now bring up onto the Top Line the next expression now you'll note that I need to remove that at sign and if I jump all the way to the end again I can put a comma and again if I bring that up to the top line I need to remove that at sign finally I can finish that off with a closing bracket so you should have three closing brackets on that expression and also an at at the beginning now if I highlight that and copy it jump back into Power automate I can paste that in and that will overwrite the existing expression that's there and it will now perform the necessary conditions to make sure that we're not equal to Saturday we're not equal to Sunday and we're definitely not equal to a public holiday now the final piece of this just as a check really I'm going to use the compose action again and this time I'm going to jump into the expression Tab and use length and that will allow me to check the number of days that's been returned back from this filter array matches with what I'm thinking so I'm going to say okay to this I'll give this a quick rename to number of days and then we'll go ahead with a test now if you remember from our SharePoint list we had the 17th and the 21st as public holidays so I'm going to request from the 10th all the way through to the 25th and hopefully we'll exclude those public holidays we'll exclude the Saturdays and Sundays and at the final action within our flow we'll get a number of days that are working days so hit done pop open the date difference we can see we've got the 15th so from the 10th to 25th is 15 days the compose has added one to give us 16 and then if we have a look at the select array we have that arrange that runs from zero all the way up to 15 so that's 16 numbers starting from zero so we then use that in the output so starting with that start date the 10th if we go all the way to the bottom we can see the 25th and then you'll notice we have the day so we can see the Mondays the Tuesdays Wednesdays we've got the Saturdays and Sundays but we've also got the 17th which is a public holiday and if I scroll down again we also have the 21st which is also a public holiday we can see the Boolean value there for True whereas the rest of them are all set to false if we move further down the flow we can have a look at the filter array it's taking that input which is the full array and in the output it should now have excluded at The Saturdays and Sundays we can see we go from Friday to the Tuesday it's also excluded the public holidays so the 17th is no longer in that array either and also the 21st which is a Friday is also excluded from that new array and if we have a look at the number of days we can see that that's actually just 10 working days based on the non-working days of a Saturday and Sunday and the public holidays that we've supplied to this flow via a SharePoint list so we're now going to have a look at how we can do exactly the same thing using a very similar logic in powerapps so I'm going to build a solution that uses very similar logic to my pile automate flow in that I'm going to create a table of data and I'm going to repurpose some of those dates so that they are nowadays and Mondays to Tuesdays or Wednesdays and also use some Boolean logic to determine if a date is a public holiday or not so with that to get started I'm going to add in a couple of date controls and that'll allow us to pick the start date and the end date and if I control C and Ctrl V that I get my two date controls on screen there to make life easier for me I'll quick double click and rename them so I'll call this this start and we can call that the end then what I'd like to do is I'll insert a button and that button is going to run all the various logic that I'm going to build to create this table of data you could of course run this when the screen loads or when the app loads but I'll leave you to make that decision and then with the insert again I'm going to put in a text table and we're going to use that to Output the number of days annual leave between these periods excluding those Saturdays Sundays and the public holidays so in the example we did on power ultimate we did from the 10th to 25th so using that ALT key I'll go and change the second date picker to the 25th and hit OK and that's me all set up ready to run with the logic that I'm about to build out next up I need to attach the SharePoint site that has the holidays so if I go and search for SharePoint pick that SharePoint data source pick that connection and my create me a list as well as my national holidays SharePoint list we can connect that and now we've got our data source all ready for action too so first things first with this button what I'd like to do is I'd like to create a collection of all those public holidays so I'm going to use the Expression clear collect and I'll call my collection core public holidays and then we will add that new data source which is my national holes there we go it pops up and I just want the dates so very much like we did in power automate I don't want all the other columns that come through I just want the date so I can do a DOT date put in the Collision bracket and the semicolon and this one now just get me back a lovely collection of national holiday dates so to test this I can see my little button hidden just in the corner there if I hold down my ALT key I can press on that button and then if I highlight my collection with my cursor we can see down the bottom here we have a data type of table and if I use this pop open here we can see all the dates that are part of our public holidays next up I want to set a variable to the number of days difference between the date picker so we'll use the Expression set I'll type in global number of days as the name of our variable and then we'll go with a calculation where the date picker between the start and the end date so if I type in date picker I actually want the ends I want the 25th so selected date minus the date picker start type in date picker and choose start there dot selected and with that we want to then add on one very much like the solution in power automate so that'll give us now the number of days between the start and the end inclusive of that start date and I'll finish things off with a semicolon so our next expression is very much like the select action in power and automate so set the global dates as the name of my variable and I'm going to use the for all expression and so then what that's going to do is it's going to Loop through my range of numbers which is going to be based on sequence and that sequence is going to be based on a number of numbers so in this case we're going to use that Global number of days which is a calculation between the start and end date plus one and what number do we want to start from well we want to start from number zero so we'll create a range from 0 all the way up to the number of days with that in place I'll use the as expression so I can refer to the value in this sequence as DV the next part is just purely defining our object or our table structure so I'll in a couple of open and close squiggly brackets as I call them and we'll put in the date value we need the day value and we also need the public holiday which will be our Boolean the true or false for our date it's going to be based on the date picker plus the value from our sequence so I'll type in date picker I'm going to go with the start date and it's the selected date that we need here and we want to add on the DV dot value and I'll put in a comma there and hopefully everything's happy looks good the day is based on that exact same expression so I could actually just highlight that and copy it and then I could use the text expression so open and close brackets on the text here paste that string in there that expression put in a comma and now I can specify the format of that date so I can put in four of these exactly the same as power automate now you could use days as numbers but I'm doing this purely to show how simple it can be if you know how to use these expressions with a public holiday it's all based on a Boolean does something exist so very much the same again we have the datepicker dot select the date plus the value is it in our collection of public holidays if it is is it true if it's not it's a false and that ends our object or our table structure we can then go and add in our closing bracket for our for all our closing bracket for our set and put in our semicolon and that is the end of that expression so it's created our lovely array or table of data based on these conditions now like before if I hold down my ALT button I can see that button sneaking out around the corner there I can press it that will run the logic and if I highlight the global dates here we can see now we have a table of data which includes the dates running from the 10th all the way through the 25th we have the days are they Monday Tuesday Wednesdays Etc and we have a public holiday column and it's based on that condition does the 17 exist yes it does does the 21st exist yes it does if you remember that SharePoint list it has both the 17th and the 21st as public holidays the final piece of this puzzle is to filter this new table of data that we've created so I'm going to do that with another expression which I'm going to call the global filtered dates and then we're going to use the filter expression here and we're going to filter our variable called Global dates so of course we want to check to make sure that the day is not in a Saturday or Sunday so I'll start with day in and then I'm going to create an array-like structure here with the square brackets type in Saturday and also in quotes Sunday and this will currently return any dates that are in a Saturday or Sunday but of course I want them not to be in so simply type in not with the open and close brackets at the end of my expression here and then the other part is to ensure that they're not a public holiday so I can type in and and we're going to say that they're not a public holiday and put in the closing bracket last closing brackets put in the semicolon and that's it that's all there is to it so again holding down the ALT key if I press that little sneaky button we can have a look at the output of This Global variable and we can see now that we have Monday Tuesday Wednesday Thursday Friday we're missing that Monday which is the 17th because of course it was a public holiday and we're also missing the Friday the 21st which is a public holiday and then as a final test if I just get rid of this expression bar if we go into this text label I can use the count rows expression to determine the number of rows that's returned by our filter dates which of course should be 10 and it is 10 so we know that between the 25th and the 10th of July based on the public holidays are in my SharePoint site and The Saturdays and Sundays there are only 10 working days jumping back onto power ultimate if we just remind ourselves there we have exactly the same results and over on to that SharePoint list we can just remind ourselves 21st and the 17th of July were in fact public holidays and therefore removed from both of our answers in parallel to me and power naps and so that's the end of the demonstration today we've learned how to calculate the number of working days between two dates both in power automate and power apps we've excluded those weekends the public holidays based on an external list and ultimately calculated the number of working days during that period thanks very much for watching on I hope you very much enjoyed today's video look forward to seeing you again sometime soon thanks for watching thank you

Channel: DamoBird365

Views: 3,528

Keywords: Power Automate, Power Apps, Working Days, Date Calculations, Workflow Automation, App Development, Tutorial, How-to, Power Platform, Automation, Date Functions, Exclude Weekends, Public Holidays, Filter Array, ForAll, Sequence, Select Action, Range Expression, Power Automate Tutorial, Power Apps Tutorial, Power Apps Tutorial, powerapps tutorial for beginners, power automate tutorial, power automate tutorial for beginners, damobird365

Id: G096DY0a_N8

Length: 23min 49sec (1429 seconds)

Published: Mon Jul 10 2023