Calculating Working Days in Power Automate & Power Apps |
HTML-код
- Опубликовано: 28 май 2024
- 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 ☕ 
Наука
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
Ive tested on another app and still the same thing
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.
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
@blackbeltphil5089 if you want to send me an example to damien@damobird365.com I can take a look. Thanks
@@DamoBird365 thank you I just sent over the full details to your email
A wonderful calculation of working days using PowerApps!
Thank you Sayed.
Love the fact you showed Power Automate and Apps together to achieve the same thing.
Cheers Richard. I quite enjoyed putting it together. I hope it helped you.
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!
You're very welcome!
This vid deserves a couple of beers Damien! Cheers!
Thanks Emmanuel- really appreciated. Very kind.
Very very nice walkthrough 👏👏👏👏
Cheers Frank 👍
Damo this was EXACTLY what we needed on a project we're delivering right now thanks mate!!!
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!
Just back from holidays myself and really chuffed with your comment - much appreciated.
Thank you for this! Exactly what I needed.
Cheers Nathan. I appreciate you stopping by.
Great tutorials. I always learn something new :)
Great video Damien. Thanks
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).
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?
❤ 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
I’m loving your responsive / modern app blogs. I need to challenge myself on that. Thanks again.
I too like the dual solution format!
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.
Hey Bruce, it might be easier to discuss with someone on the forum powerusers.microsoft.com/ good luck 👍
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.".
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 👍
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.
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.
@@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.
@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.
Very useful video! Thank you Damien for sharing🙂
Cheers Gustav, I hope you learned something.
@@DamoBird365 I really did, I'm working on a PowerApps project where your code fits perfectly. Thanks again!
Just seen the LinkedIn post. www.linkedin.com/posts/gustav-kaldner-4b923798_powerapps-powerapps-microsoft-activity-7087692641244459009-YF4L. That’s a nice looking app.
@@DamoBird365 Thanks I really appreciate that you liked what you saw 🙏🙂
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?
This is how you do it from a Power App
ruclips.net/video/L67m8wgd4Ak/видео.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.
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.
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.
Ooooh I like it 👍
@@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 😁
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.
@@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.
Many thanks!
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
I hope you did learn a lot 👍
@@DamoBird365 yes , have corrected the autocorrect
If you're still looking for ideas on your number of days - check out ruclips.net/video/ybPWtu1i1j0/видео.html
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
Add(datediff,1) would probably work for your scenario.
@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.
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.
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.
Good question by the way. I appreciate you asking as it will help others too 😉
@@DamoBird365 thank you, I thought it was something like that. Hope you can feel refreshed by holidays and other events this July!
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
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?
In Power Apps or Automate? I like the challenge. Have you tried the official forum?
What if the public holidays is more than one day? How can we calculate any day that fall within that range of days?
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.😢
You would need to provide more detail. Maybe ask on the forum powerusers.microsoft.com/
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
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
Hey bro, do you get some help with this problem? i have similar one
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
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 🤷
@@DamoBird365 Thanks for support.
It works fine, but everything still needs to be converted to an integer.