Calculating Working Days in Power Automate & Power Apps |

Рет қаралды 6,806

Күн бұрын

In this tutorial, I'll show you how to calculate the number of working days between two dates, while excluding weekends and public holidays.
In Power Automate, I'll walk you through the process step-by-step, utilizing the Select action, range expression, and Filter Array action. This powerful combination will simplify your workflow and save you time.
For the Power Apps enthusiasts, we've got you covered too! I'll demonstrate how to achieve the same result using the Sequence, ForAll, and Filter expressions. My solution is easy to adapt and will make your app development process a breeze.
Whether you're a beginner or an experienced user, this tutorial is designed to help you enhance your skills and streamline your projects. Join me as we explore the ins and outs of calculating working days in Power Automate and Power Apps.
Don't forget to subscribe to DamoBird365 for more insightful tutorials, tips, and tricks on Power Automate and Power Apps. Leave your questions and comments below, and we'll be happy to assist you.
0:00 - Introduction
0:54 - Calculating working days in Power Automate
01:54 SharePoint List of Public Holidays
05:39 Using select to build an array of dates
10:55 Advanced filter array expression
14:39 Calculating working days in Power Apps
17:53 Using forall to build an array of dates
23:04 Conclusion and outro
Connect with me:
Website: www.DamoBird365.com
Twitter: DamoBird365
LinkedIn: www.linkedin.com/in/DamoBird365
#PowerAutomate #PowerApps #Tutorial
Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕

Пікірлер: 78

@blackbeltphil5089 9 ай бұрын

I have come across a very odd issue with calculating work days. For March specifically, if the month starts on a Thursday or Friday it calculates correctly, but any other day of the week and it always records 1 working day less for that month. Its specifically March too. Has anyone else come across this? Can anyone else test? The weird thing is, it originally worked, but has since broken

@blackbeltphil5089 9 ай бұрын

Ive tested on another app and still the same thing

@DamoBird365 9 ай бұрын

Power App or Power Automate method? I can’t explain either to be honest. I would want to see your arrays or table of data to understand why. You should be able to see the data that have been kept after a filter.

@blackbeltphil5089 9 ай бұрын

The PowerApps method. Coincidentally I work with a colleague of yours she just informed me when i showed her your video. The table shows that its only counting up to march 30th, it ignores the 31st for some reason but again only when the month starts on specific days

@DamoBird365 9 ай бұрын

@blackbeltphil5089 if you want to send me an example to damien@damobird365.com I can take a look. Thanks

@blackbeltphil5089 9 ай бұрын

@@DamoBird365 thank you I just sent over the full details to your email

@SpikeyMike1 6 ай бұрын

I never comment on videos, but - Thank you!! Not only is this the exact thing I need to do myself, but you've explained it in such a clear and concise way that it's easy to *understand* rather than easy to *copy*. Far more valuable!

@DamoBird365 6 ай бұрын

You're very welcome!

@richardclowes6166 6 ай бұрын

Love the fact you showed Power Automate and Apps together to achieve the same thing.

@DamoBird365 6 ай бұрын

Cheers Richard. I quite enjoyed putting it together. I hope it helped you.

@emmanuelmaceda2475 Жыл бұрын

This vid deserves a couple of beers Damien! Cheers!

@DamoBird365 Жыл бұрын

Thanks Emmanuel- really appreciated. Very kind.

@1979nathanrose 10 ай бұрын

Damo this was EXACTLY what we needed on a project we're delivering right now thanks mate!!!

@WaliSayed 4 ай бұрын

A wonderful calculation of working days using PowerApps!

@DamoBird365 4 ай бұрын

Thank you Sayed.

@emmanuelmaceda2475 Жыл бұрын

Just came from a long vacation and very happy to see this new vid of yours 😊Great as usual Damien! I was working on something similar two months ago but seeing I'll be able to steal some of your methods here. Thanks so much for sharing!

@DamoBird365 Жыл бұрын

Just back from holidays myself and really chuffed with your comment - much appreciated.

@UncleBubba Жыл бұрын

Great video Damien. Thanks

@MarcinK-wj5js Жыл бұрын

Great tutorials. I always learn something new :)

@nathanhaws5838 8 ай бұрын

Thank you for this! Exactly what I needed.

@DamoBird365 8 ай бұрын

Cheers Nathan. I appreciate you stopping by.

@franknielsen3219 4 ай бұрын

Very very nice walkthrough 👏👏👏👏

@DamoBird365 4 ай бұрын

Cheers Frank 👍

@ItsMeProday83 Жыл бұрын

Love your videos Damien..keep up with great job)!! I'd like to see how to retrieve (on monthly basis) number of days that every employee used for holiday in that month (report needed for our HR).

@DamoBird365 Жыл бұрын

Can you share with me how you store that data? Is it in a list or table? Do you have an item/record per request? Is it a start and end date with the number of working days already calculated or do you want this solution to calculate the days between dates and also total them per person?

@brucemarriott3841 4 ай бұрын

This a great help thanks so much, your teaching style is spot on thanks. I am thinking I will use this but kicking it off with a Timed Action (1am each day) based on a Date field being empty, then calculating the difference between TODAY and another Date. I have a Holiday List to read in non working days. I am lost as to which order to do things. Hope I am not asking too much but still a beginner with PA. Keep up the good work.

@DamoBird365 4 ай бұрын

Hey Bruce, it might be easier to discuss with someone on the forum powerusers.microsoft.com/ good luck 👍

Жыл бұрын

❤ Great Demo as always Damien, kudos for PowerFx in PowerApps and using the analogy with Power Automate. Loved this concept and eager for more. #repurposedtriggered

@DamoBird365 Жыл бұрын

I’m loving your responsive / modern app blogs. I need to challenge myself on that. Thanks again.

@McIlravyInc Жыл бұрын

I too like the dual solution format!

@GaryCarmen Жыл бұрын

Love this idea and concept. We then took it one step further to provide flexibility, given that some people work less than 5 days etc... so we built in check boxes for days to include / exclude and whether you want to include / exclude public holidays, we stored the day selections in a collection and used that for the filter. Then the output shows the number of days and actual dates in a gallery view.

@GaryCarmen Жыл бұрын

Damien - if you ever want another challenge, how about reverse engineering this. The scenario being that we want to calculate a date based on expected working days to complete a piece of work taking into consideration working days and bank holidays. For example, we need to complete a contract and it will take 20 days, the start date is the 20th July and we want to provide an expected completion date based on working 4 days a week and excluding bank holidays.

@DamoBird365 Жыл бұрын

Ooooh I like it 👍

@GaryCarmen Жыл бұрын

@@DamoBird365 😀- We have seen quite a few requests for calculation of deadlines. I have now taken the app another few steps further by adding another date picker to add and store dates people would like to exclude. Also to select public holidays based on location e.g. UK, Spain, Germany etc.. It gets pretty addictive this stuff 😁

@DamoBird365 Жыл бұрын

I really like this suggestion. I can certainly visualise how it might be achieved in both. Are you planning/creating tasks? You could assign a person or work pattern to multiple tasks and then determine the deadline based on number of working days, excluding hols, based on locale. Lots of interesting ideas.

@GaryCarmen Жыл бұрын

@@DamoBird365 - the creation and planning of tasks would be a great addition, as it could then have the flexibility of determining deadline dates based on a series or number of parallel tasks.

@gustavkaldner5248 Жыл бұрын

Very useful video! Thank you Damien for sharing🙂

@DamoBird365 Жыл бұрын

Cheers Gustav, I hope you learned something.

@gustavkaldner5248 Жыл бұрын

@@DamoBird365 I really did, I'm working on a PowerApps project where your code fits perfectly. Thanks again!

@DamoBird365 Жыл бұрын

Just seen the LinkedIn post. www.linkedin.com/posts/gustav-kaldner-4b923798_powerapps-powerapps-microsoft-activity-7087692641244459009-YF4L. That’s a nice looking app.

@gustavkaldner5248 Жыл бұрын

@@DamoBird365 Thanks I really appreciate that you liked what you saw 🙏🙂

@learningeachday 4 ай бұрын

Damien, thank you for teaching us all. I have learned so much from you, and agree with others that you stand out above the rest because you truly explain what's happening-- you don't just give answers. You're teaching us to fish instead of giving us a fish (as they say)! Truly appreciate all that you do. On this video, I was following along and frankenstein-ing where needed, but I am running into a hitch on the filter array. I've followed your formatting, but Power Automate says that the expression is an improper boolean something or other. If I include the "@" symbol, it yells, "The input parameter(s) of operation 'Filter_array' contains invalid expression(s)." and to fix it... If I exclude the "@" (because I know from other videos that pesky @ symbol always seems to create issues), then when I test it, it gives me the improper boolean message: "The execution of template action 'Filter_array' failed: The result 'and(not(equals(Monday,'Saturday')),not(equals(Monday,'Sunday')),not(equals(False,true)),not(equals(True,true)))' of the evaluation of 'query' action 'where' expression 'and(not(equals(@{item()?['DayOfWeek']},'Saturday')),not(equals(@{item()?['DayOfWeek']},'Sunday')),not(equals(@{item()?['Holiday']},true)),not(equals(@{item()?['ClassDay']},true)))' is not a valid boolean value.".

@DamoBird365 4 ай бұрын

Was your flow created in the new designer? 🤔 I think I’ve seen your problem as a bug. And thank you for your kind comments. Made my day 👍

@learningeachday 4 ай бұрын

Ok, I think I've distilled it down to the "@and" being the issue... but that means I can't add multiple.... cue deep sigh.

@DamoBird365 4 ай бұрын

You didn’t tell me if it’s the new designer? Do the dynamic pills get added to your expression? @ and {} are used for inline expressions or in trigger conditions. I’ve seen new designer add pills 💊 into expressions and it doesn’t save.

@learningeachday 4 ай бұрын

@@DamoBird365 (Sorry! Didn't see your reply when I typed mine! So strange how the timestamp is off...) Anywho-- I'm so happy my comment made your day! I just told my coworker about you and how you are the best. Ha ha. On your question... Yes, I'm using the new designer, and it does indeed add @ to the beginning of the expression when I transition from the default to the advanced mode. Then when I copy the expression (like in your video) and paste in my notepad, it adds @{ to any dynamic values (i.e. @{item()?['something'] ). As long as I use the default filter query basic mode (even if I do switch to the advanced mode and see the "@" at the beginning ), it works fine. But as soon as I touch it (even to erase and re-add "not" for example at the beginning of " @not "), it rejects the whole thing.

@DamoBird365 4 ай бұрын

@user-xi4bx4zn5f it’s a bug. I saw this and will report it 👍 it’s when you use advanced. Thank you. It should not have the @{} and if you remove all instances it will save.

@McIlravyInc Жыл бұрын

Many thanks!

@McIlravyInc Жыл бұрын

Doesn't quite meet my use case which is finding x number of work days after a date for a due date. However I did learn a lot more about arrays and things that helps me on my journey thank you

@DamoBird365 Жыл бұрын

I hope you did learn a lot 👍

@McIlravyInc Жыл бұрын

@@DamoBird365 yes , have corrected the autocorrect

@DamoBird365 Жыл бұрын

If you're still looking for ideas on your number of days - check out kzfaq.info/get/bejne/r8iAitemls-VmmQ.html

@sayedsuhail9513 23 күн бұрын

This is such a fantastic video, How many times I end up on your videos while dealing with actual customer requests is insane! How do I send you a check?

@DamoBird365 23 күн бұрын

Thanks Sayed 👍 you’ll have to hit me with ideas when you don’t find the answer too 😉

@geralddahl9159 Жыл бұрын

For Power Apps forAll expression why is is 1 added to startDate? 19:25 Thank you for showcasing how to do this in both Flow and Power Apps, much appreciated.

@DamoBird365 Жыл бұрын

It’s because we want to get the dates inclusive. 10-15 July = 5+1. A sequence of 6, starting from 0 is 0,1,2,3,4,5. 10+0=10, 10+1=11, … 10+5=15. Hope that helps with understanding.

@DamoBird365 Жыл бұрын

Good question by the way. I appreciate you asking as it will help others too 😉

@geralddahl9159 Жыл бұрын

@@DamoBird365 thank you, I thought it was something like that. Hope you can feel refreshed by holidays and other events this July!

@psychedelicrelaxation1509 9 ай бұрын

Thanks for adding much valuable content, I have a question though, if I have start date and end date as same it shows as error 'The first argument to sequence must be between 0 and 50,000' I am using this if user selects only one day. Can you please help me out here

@gavacongg7 10 ай бұрын

Hey Damien, I need your help and guidance with scenario. I have an excel template setup like a cheque/invoice and periodically I get a listing of students for whom I must prepare an invoice for each person. Could automate the process so that each time I receive a new list of students an invoice automatically populates and I can scroll through each and print them individually or all at once similar to a mail merge?

@DamoBird365 10 ай бұрын

This is how you do it from a Power App kzfaq.info/get/bejne/gpxnoJuozMqYcZ8.html you could choose your method and simply loop through the sheet to create multiple invoices. More video links in the description of above link.

@roma9026 3 ай бұрын

Thanks for this video. My use case is slightly different: I have an edit form with Duration (number), Start Date (date) and End date (date). Now I want to add a number of days in text input for duration, then select the start date and that should give me the end date excluding weekends. How can I achieve this?

@DamoBird365 2 ай бұрын

In Power Apps or Automate? I like the challenge. Have you tried the official forum?

@andriybabkin 4 ай бұрын

What if the public holidays is more than one day? How can we calculate any day that fall within that range of days?

@manojpai1988 4 ай бұрын

Good Day!!@DamoBird365, thank you for the Video its really helpful. But this does not work when the ComposeDateDiff output is 00:00:00. For Example the user applies for a Sick Leave which is for a day that is Yesterday to Yesterday which is 1 day but the difference value is 00:00:00. Can you assist me on that please

@DamoBird365 4 ай бұрын

Add(datediff,1) would probably work for your scenario.

@manojpai1988 4 ай бұрын

@DamoBird365 tried it but since the output of date difference is a string it's not adding, tried add(int(datedifference)),1 but there is error.kindly assist.

@dexterlee1199 9 ай бұрын

Thanks for the great video may I ask if I'm going to also calcuate the working hours how shall i do the hours need to be from like 830-1730 is working hours total 8 hours and 1 hour from 1230-1330 is rest time

@DamoBird365 9 ай бұрын

That is an interesting use case and challenge. Would you provide a date picker for start date, dropdown for starting time, followed by the same for end date/time and then want to calculate the number of working hours excluding a fixed time for lunch?? I think you are going to have to split into working days and then working hours. Is your use case for Power Apps or Power Automate or either? I can't promise to do a video on it but I like the challenge. The other place to ask is the forum. powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

@zarquinho 9 ай бұрын

Hey bro, do you get some help with this problem? i have similar one

5 ай бұрын

Good day, I came across that when the start date and end date are the same. Then the split function will not return a number. Probably because the difference is 0. So the result of the differences of two dates is 00:00:00 and therefore the separator (.) is missing. How to fix it? Thanks

@DamoBird365 5 ай бұрын

You could try this split(split(datedifference(utcnow(),adddays(utcnow(),1)),'.')?[0],':')?[0] it will split on . first and then : after and should return either the days or 00 ? other option is formatNumber(float(split(datedifference(utcnow(),adddays(utcnow(),1)), ':')[0]), '0') - wish I didn't think of splitting on : in the initial demo 🤷

4 ай бұрын

@@DamoBird365 Thanks for support. It works fine, but everything still needs to be converted to an integer.

@soohyunmoon6995 Жыл бұрын

I wanted to try to get a date in excel sheet with power apps datepicker.selecteddate by using power automate. But it didn’t work…. If available, I want to know the way of date to be equal.😢

@DamoBird365 Жыл бұрын

You would need to provide more detail. Maybe ask on the forum powerusers.microsoft.com/