I can't tell you how grateful I am to your videos! You've literally saved my sanity! Would love to collaborate with you at some point. Thank you for explaining things so straightforward!
Once again, THANK YOU!! I wasn't sure where to even begin with this formula. I took it a step further and used this to calculate available hours for associates each month for a capacity report. You always help me with my Power Apps obstacles!
Great video, Note for Viewers: please make sure while testing the app (Means not published), Please start App from " Run OnStart ", so that your holiday collection will be made easily And You can test it finely and smoothly. TmeStamp 5:50
Hi Reza, Firstly good tutorial. Just a quick one. The days calculated in your example from the 6/26 to the 6/30 should be calculated as 3 days as start to end. I had the same issue. The workaround that I did was to add a day to the end date. As such RoundDown( DateDiff( DatePicker1.SelectedDate, DateAdd( DatePicker2.SelectedDate, 1 ), Days ) / 7, 0 ) * 5 + Mod( 5 + Weekday( DateAdd( DatePicker2.SelectedDate, 1 ) ) - Weekday(DatePicker1.SelectedDate), 5 ), This resolved it. Just a suggestion to anybody who has been in the same shoe. Cheers.
Hi Reza, thanks for the great explanation. This makes it easy for a beginner. I wanted to find out how to include half day calculations, assuming I applied for half day leave instead of a full day…?
Thanks. I have done this video long time ago. You will need to look at the formulas put in play and manipulate it for half day logic. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi thanks for such a short and precise explanation where it is much simpler than many others. Just wonder, RemoveIf(DS, to exclede weekday), it is pretty straightforward. Just curious, how RemoveIf can detect CountRows(Filter..holiday) > 0? Wish to know how does it really work out though it looks simple. TQ
@@hpkeong That checks to see if the date falls in the holidays collection range. If it does then remove it. To check if it does, Im using CountRows(filter condition) > 0 Basically if that date does exist in that holiday range then countrows will return a number greater than 0
New to PowerApps. Thank you for this wonderful tutorial. I've applied your logic to two date pickers where if a particular date is selected on datepicker 1, then it would dynamically populate datepicker 2 (in view mode). Question please. How would you adjust this logic where onchange of datepicker 1, it would dynamically populate datepicker 2, 3 and 4 on the same form with different dates? I tried passing differing number of records in Last(FirstN( colDateRange[,varNumberOfRecords ] )).Date, in each datepicker, but each time Last pulls the last record of colDateRange. Would I need to create a different collection? For example, datepicker 2 shows values: Last(FirstN( 121records,121 )).Date which pulls the 121st record, datepicker 3 I have Last(FirstN( 121records,108 )).Date, but it pulls the 121st date instead of the 108th date. Appreciate any direction.
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani Thank you, I will try that. I tested using a label and when I hard code the second value that passes the number of records to return, I see now that it is returning the indexed row excluding the omitted holidays. I'll watch your video again as I must have missed a step. So it's working, it's just returning a null value because the number of records I am passing is greater than the data set because the holidays and weekends have been omitted. So the last record it returns is indeed the last record of the entire dataset, not the date that corresponds to the "RowIndex" value.
Great video. I know it's a few years old, but is there a way of doing the holidays lookup when the formula is being created within a table column within a model-driven app? I have UK bank holidays listed in a separate table within my solution, but I can't get the formula to find it when I try to reference it as part of the calculation. Thanks in advance!
Hello Reza Thank you for educating us on the nuances of Power automation - I need your help with a quick query I am using a SharePoint list where I have start and end dates below are the things that I wish to achieve 1. Calculate the difference between the two dates that includes the start date and excludes weekends and holidays if any 2. There is no holiday column in my list - how to exclude those holidays
This video is on Power Apps not Power Automate. I do not have a video reference on your scenario. I have seen plenty of solutions on forums related to this. I will recommend checking or posting your query on forums at powerusers.microsoft.com
@RezaDorrani Hey Reza, love your content! Just wanted to let you know that your code isn't working for a specific test case. If my start date is on Sunday(Weekday Value = 1) and end date is on Friday(Weekday Value = 6) then the difference is 5. So (5 + 5) Mod 5 = 0. Then it gives value as 0 instead of 4.
Hello Reza, This was really helpful! Thank you! A quick question - how do you take into consideration business hours, say 8am-5pm so that after COB the tickets are placed on hold and would continue reading next day upon Start of Business (8am)?
I would need to do a separate video on that topic and would need to try it out myself to know the steps. I would recommend checking on forums in case someone has done something similar powerusers.microsoft.com
Excellent! I always enjoy watching video. Was very much helpful. I'm looking for something like to disable all days and enable only a particular day in a week. Can we achieve that? Thanks in advance!
I believe there is a start date like property in calendar control. It will allow users to pick from that date onwards. Alternatively, you can always let user select any date and on submit validate the date and accordingly display error message.
Thanks for another great vid. How could I check holidays for the next 30 days instead of current year? At the end of year there are holidays each side of 12/31
Thanks. Its been a long time since I did this video and do not recollect the formulas. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, could you pls make a video on how to calculate estimated end time for each task, if we have start time and duration in hours as an input and then exclude weekends & Holidays?
I make videos when I receive multiple requests on a topic. This is the first request on this topic. I have video ideas in my backlog with over 50 requests. Il recommend checking on forums in case someone has done something similar powerusers.microsoft.com
This has been really helpful and so easy to follow. A challenge I am having, however, is reverse engineering it so I can find the "start date." For example, I have the date for which a project is due and based on the known X amount of days a task takes, I need to find when the team needs to start the task. So, instead of KnownStartDate+KnownDuration(excluding weekends/holidays) =Due Date, I need KnownDue Date - Duration(excluding weekends/holidays)=Start Date. Is this something you know how to do?
Greetings, I love this! However, I was wondering if there was a way to have a different day then Today()? For example a Date Field in the Form that Changed based upon an entry. Then the future date would be determined using this method and the user inputted date. For example: User Input: 8/21/2023 DropDown: 84 (days) Output: Future Date (minus weekends and holidays)
I have not tried this scenario but I guess it should be possible. I would recommend posting your query on forums in case someone has done something similar powerusers.microsoft.com
@@RezaDorrani Hi Reza, first off, thank you so much for your invaluable help with all things PowerApps! ❤ I struggle too with the concept of replacing Today() with a Date Field from a sharepoint list. Is that something you can help us with or point us in the right direction please? Again, thanks a million!!
@@Kinethik Thanks so much. I have not tried this scenario hence not sure what the steps are. I would recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Thank you! How do you create dates in Power Apps configured to your data source, rather than using the default date picker. Calculates the number of dates: Submission dates minus Approved dates.
Hi! Thanks for this, it helps. One small "issue", for example if I import list of Holidays to the table then some of them may occur on the weekend (eg. Independence Day on Sunday). So this algorithm may substract one day twice, once as weekend, once as Holiday, right? I have to exclude Holidays that occur on the weekend from the list or from the algorithm.
You would need to update code to accordingly remove the holidays that fall on weekends. I would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hey Reza, Great work I was able to learn just because of your videos. In excluding weekends I have a doubt, For getting the exact dates say need different from 6/Feb/2023 to 8/Feb/2023 according to your video we will get only 2days but it should be 3days. So for that I have added +1 to the end date datacard. Here has the issue, when I select Monday as start date and end date as Friday of same week. I'm getting 0 as value instead of 5. How can we resolve this?
It’s been a while since I did this video. I would have to try it out and to see what could the fix be. I recommend posting the issue with screenshots on the forums at powerusers.microsoft.com
You can define the start of your work week in the Weekday function - docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datetime-parts#description
Hello Reza, first of all I want to thank you for all your contribution! it's great. I'm power apps beginner, and wanted to know if there's a solution to a simmilar case in which we need to add 2 workdays based in a date picker card instead of a label item?
Which box? I would have to try it out to provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
This was a brilliant guide on how to compute business days. I just have a problem, the collection is not automatically running when published. what should be the problem?
@@RezaDorrani I am also trying to figure it out. It works perfectly when I manually clicked Run app Onstart but when it is published, it is not computing.
Thanks again Sir Reza, do you have some sample formula where it identify conflicting dates, for example, in reservation or leave application, user applied from january 1 to 5 but there is someone already filed from Jan 2 to 4 , appreciate your reply thanks again. :)
I do not have a video reference on this scenario. Idea would be to query datasource to check if any item exists with same dates and accordingly display error message when user tries submitting the item. I will recommend checking on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza, this has been really helpful and so easy to follow. A struggling I am having is that i have differents requests and each one have different days to reply, so, i have today date (TodayDate) and based on the days of each request, i have to calcute the date response (DateResponse). So, i need your help please. For example, i have a request 1 and this have a 3 business days for response, so the first calculation is DateAdd(TodayDate, BusinessDays,Days ) but i dont know how to use this RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 + Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) in the formula before. Because my endDate is previusly calcuted with the first formula. Is this something you know how to do? Thanks
Thanks for watching this video. I'm sorry but I did not understand your use case. It would be much easier to understand with screenshots. I would recommend to post your issue/query with screenshots on the forums at powerusers.microsoft.com
Very nice video! How is it possible to group your calculation by Year(date) && Month(date), so you can sum it up in a gallery or chart? I have tried a million different ways, but can't figure out how to input the logic. Either I end up with wrong result or the result is the total days as when using the calculation on label. Very hope you can help ! Thanks
I am not sure I understood the use case. It would probably be easier to see a few screenshots of what you are attempting. Best option would be to post your query on the Power Apps forum - powerusers.microsoft.com/t5/Building-Power-Apps/bd-p/PowerAppsForum1
@@RezaDorrani Thanks Reza. I already tried Power Users forum, but I did not receive any response. However I think perhaps I figured out how I can get to the result. Screenshot of example: ibb.co/8d9C6XK Description: We have around 800 people who are being assigned to our different projects with a startdate, enddate, country, allocation and daily rate. Each month we have a financial status where we compare the actual cost and estimated cost - example February 2021. To be able to do that I need the business days for each assigned person split in to month over month. By multiplying this with a daily rate result in total estimated cost for the particular month. Formulas: To your colDateRange I added two columns. - First Date of month - Last Date of month. Filtered out/removed Dates, which are public holidays as pr. your examples in video. Then added below to a gallery: Filter( AddColumns( GroupBy( colDateRange; "Firstofmonth"; "Endofmonth"; "TheItems" ); "BusinessDaysByMonth"; CountIf( TheItems; Date >= Firstofmonth; Date
Hi Reza, I have a question on the date. I managed to create the date box but the placeholder date shows 1/1/2001. Would I be able to use Today's date for the placeholder?
First of all I would like to thank the video that solved a problem for me. Now I have another problem that I don't even know how to explain but here it goes. I want to limit the time of a registration. If the date/time selected in the "datepicker" is greater than a certain time, it will no longer allow registration for the following day but it would be possible to do it for the remaining days and it would give an error message "It is no longer possible to register for the chosen day". Can you help thanks in advance
Thanks for liking the video. I will have to try out your scenario to provide any guidance. I do not have any video reference for it. I would recommend to post your query on the forums at powerusers.microsoft.com
I would have to try it out to know the steps. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza... I have hunted through the videos you have on date related stuff, however cannot find an answer to an odd problem. I have a form to build and I was using set() to assign variables for values as they stay put when moving into other screens etc (collections were problematic with patch). I have a field which is a text field that is prefilled with text(today(),"dd/mm/yyyy"). It is captured in the variable fine. However when I get to the final review form which uses all the created variables to populate the fields (which are then posted to Sharepoint), every field is happy to be assigned a variable except the date field. I have tried changing the Sharepoint field to single line text and as a date field. This hasnt made saving possible. When the final form is loaded the default field for the job date is a date/time field. I can change the datacard value to the set variable, but instead of showing it as the date in the variable it shows as a different date for reason. (It's a date but its about 12 months ahead of the actual date ie 21/05/2022 becomes 9/5/2023 which is bizarre). It's a big ask, but do you have any idea how to resolve. I did use your video on the photo converted to a sharepoint attachment which works perfectly, so the form updating the SP list actually has 2 data sources that feed it. They all work except the date for some reason. (Technically I can dump the field and use the "created" field instead if there's no workaround. Thanks. Tony
Best I can do on chat is guide. I cannot know about your issue without looking at your App in action. My guess is something to do with the data card and the way your are settings its default value or update property. Date going 12 months ahead sounds weird. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani I will do that... I have created a work around by simply setting the form date to the current date so that I don't need to use the variable. This is more of a problem of why did 99% work but not this one thing.
I will have to look at your App in action to know why you are getting all those errors :) I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
I tried again and it worked eventually. I've started working with Powerapps and still get a little confused with separators because the app isn't in English. Thanks very much for sharing.
Hi Reza, Please assist, I have a helpdesk app that needs to escalate ticket to a manager if the ticket is not resolved within an SLA of two days. I am using a sharepoint list to store my tickets data. Any idea as how can I calculate this and able to send an email.
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
What do you do when you are starting say Dec 26 and you are going 14 days out. It is looking at the current year, but New Years Day is in the next year? Thanks.
Notice that the partial week calculation given above will not work if the start or end dates fall on weekends; in this case we’d need to decide what to do (either use the previous Friday or the following Monday) in the calculations - and that will depend on the application.
Dear Reza, I am tuck in very strange error while using datepicker, i have 2 containers on screen,i am using datepicker in right container. when i run app, and click on datepicker. it expands the datepicker in top left corner of screen.. i need your help on this
Not sure about this one. I will have to look at your App in action to provide any guidance. I will recommend you post your issue with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani The scenario is I have 2 date pickers. I am suppose to collect the dates between those. But the weekends should be excluded. How do i do it ??
Hi, i applyed the leave days calculation to my form and it looks perfect, however it will be calculating in days in minus if start date is after end date, i tryed with wrror message validation but it misses up my calculation
Validation would be the way. I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani Hi Sir, I have a column in sharepoint list that has submitted dates of every employee for there activity monitoring and the problem is how can I filter/collect all the skipped submit dates of employees. This is the first time I tried this also. Ex.: Submitted Dates = 05/01/2022, 05/02/2022, 05/05/2022 "Problem collect the skipped dates" Skipped dates = 05/03/2022, 05/04/2022
@@jumilbolhano3910 I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
I would like to know if I can put criteria to add 2.5 leave per month to the current leave balance. Let's say if I'm applying leave for after 3 months, my current leave balance should also add 7.5 leave days for the coming 3 months.
Can be done. You would have to get the current leave balance from your systtem of record and add 2.5 days per month to your leave balance. This would need to be done on a monthly basis by an automated job. Power Automate (flow) is your answer.
Possible but I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hello Rezza, Great video as usual, Passing date into Flow as parameter throwing error for SharePoint date column. Code - Text(DataCardValue13.SelectedDate,"[$-en-US]mm/dd/yyyy","en-US"), ---- Error - string 'mm/dd/yyyy/' does not validate against format date, Any thoughts on this..? In flow I've used formatdatetime. But same result. Appreciate your reply on this. Thank you.
This video does not use flow to pass params. However, make sure you are passing the correct parameter to flow from Power App based on data type defined in flow. You can just send selecteddate. No need for parsing and use it directly in flow. Both flow and Power App work with UTC.
@@RezaDorrani Thank you for your reply.Yes, as the video is on Date calculation, thought to check with you. Text(DataCardValue13.SelectedDate, "[$-en-US]yyyy-mm-dd"), worked for me. Initially was not working. Thank you once again..
I am doing a gantt chart in days. how to calculate the x and width value? i still doing it a month already. but still cannot. i dont want to use powerbi. can u teach me? please
Hi Mohammad, I did not understand your question clearly. You can post your question in detail alongwith screenshots on the community forum - powerusers.microsoft.com/t5/General-Discussion/bd-p/PowerAppsForum1
hii Reza hope u r doing well i jusnt want to know i m taking an drop down and i want to show today +5 days form today means a whole week but i want when ever a user select the drop down He/She can only see the week days like for ex: todays date 1 and till 7 there is week off on 6 and 7 like Saturday and Sunday so now i want the drop down have to except the 6 and 7 and shows the 8 and 9 which is next weeks monday and tuesday please help me in this
I would have to try your scenario out to know the steps and provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
@@quratulainmansoor8776 Not everything can be updated. There are many limitations with the date control. There are some samples of custom date controls on the forums that allow modifying the colors. Check the forums powerusers.microsoft.com/
Hi Reza How can I display a time in a canvas app based on Internet Time or Server Time. (Which does not use the API) The problem is when I used the Function Now() it displays the user's local time in his/her PC. So everytime the user change their time, the Time in Canvas app will also change. I am creating an Attendance Tracker I don't want the user's to cheat in their Time-in or Time-out by changing their device or pc Time. I hope anyone can help me. Thank you.
The Second query Is not Working, I'm Getting an error like { The function 'Clear' has some invalid arguments. Name isn't valid, 'colDateRange' isn't recognized......Clear(colDateRange);
Hi Reza, I need your help big time and hoping you would help as I couldn't find any solution by googling. Currently, I'm implementing event form in which I need to calculate total hours between 2 dates, time and minutes. If date is same then calculate hours using Time and Minute otherwise calculate hours based on Date, hours and minutes between 2 dates. eg:- Start Date: 04/24/23, Hour: 8 AM, Minute: 30 and End Date: 04/24/23, Hour: 12 PM, Minute: 30 should return 4 hours otherwise number of hours divided by 8 as 8 hours is the longest event time per day. Could you help me with formula?
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking or posting your query on the forums at powerusers.microsoft.com/
Hello! How to find the number of hours worked by employee ABCD knowing that we have a table that looks like this: Employee || Date ||Start time ||End time ****************** ABCD || 01/01/2022 || 08:00 || 11:01 ABCD || 01/01/2022 || 12:05 || 15:00 ABCD || 01/01/2022 || 15:55 || 18:16 Please help me.
I do not have a video reference on this scenario and will have to try it out to provide guidance. I recommend posting your query with screenshots on the forums at powerusers.microsoft.com
If you have any SharePoint related issues/queries - post on the forums at techcommunity.microsoft.com/t5/sharepoint/ct-p/SharePoint If you have any power platform related issues/queries - post on the forums at powerusers.microsoft.com/
This video is very helpful. Unfortunately i am not getting the holidays and weekends removed. Any idea where i could go wrong? I just hardcoded 30. Here is my collection formula ClearCollect( collHolidays, Filter( Holidays, Year(HolidayDate) = Year(Now()) ) ); Clear(collDateRange); ForAll( [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30 ], Collect( collDateRange, { RowIndex: CountRows(collDateRange) + 1, Date: DateAdd( Today(), Value ) } ) ); RemoveIf( collDateRange, Weekday(Date) = 1 || Weekday(Date) = 7 || CountRows( Filter( collHolidays, HolidayDate >= Date, HolidayDate 0 );
@@vinayayinapurapu what was the formatting issue? i can't figure out my error. my coding removes the weekends but won't remove the holidays. colHolidays and colDateRange collect the data correctly, but RemoveIf returns a null set when i add the CountRows Filter
Hi Reza.. I have a sharepoint for issue tracker for my team.. All I want is to create a date column which will be a calculated field based on created date. (That will exclude weekends) If created date is 1st january then due date will be 1st jan+ 2 days = 3rd january. Incase 1st Jan is on Friday then due date will be 5th January ( excluding Sat and Sun day). Please help me to create this column in sharepoint.
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
@@thewanderingcouple8378 Forums is a free platform. You may or may not get a timely response. If this is time sensitive, then you need to look at getting consulting help.
Hi there, this is very userful topic. However if the user raise a critical priority ticket at EOD, the due date must be shifted to next day {because critical ticket to be addressed on the same day). Is that possible? Say for example, if the business hours is between 9 AM to 5 PM, and user raise a high priority ticket at 4 PM, the due date must be shift to next day. Please guide
Possible. In case of time calculations the formulas would get a lot more complex though. I do not have a video or use case on this specific scenario. Forums will be your best bet - powerusers.microsoft.com/
I did not get this issue. I will recommend to post your issue with screenshots on the forum at powerusers.microsoft.com/t5/Building-Power-Apps/bd-p/PowerAppsForum1
@@RezaDorrani , My issue is , difference of start date (already there in the records) and Today() is showing some wrong value when Today() day is weekend, otherwise it is working correctly . For example if start date is 22-march-2021(Monday) and Today () is 27-march-2021(Saturday), the actual difference value is 4, but is showing 0.How to overcome this situation?
Brilliant! Thanks for sharing and explaining it in detail as well.
Thanks for watching :)
I can't tell you how grateful I am to your videos! You've literally saved my sanity! Would love to collaborate with you at some point. Thank you for explaining things so straightforward!
Thanks Clark
Once again, THANK YOU!! I wasn't sure where to even begin with this formula. I took it a step further and used this to calculate available hours for associates each month for a capacity report. You always help me with my Power Apps obstacles!
Awesome! Thank You for following my videos :)
Great video, Note for Viewers: please make sure while testing the app (Means not published), Please start App from " Run OnStart ", so that your holiday collection will be made easily And You can test it finely and smoothly. TmeStamp 5:50
Thanks for the tip!
Hi Reza,
Firstly good tutorial. Just a quick one. The days calculated in your example from the 6/26 to the 6/30 should be calculated as 3 days as start to end. I had the same issue. The workaround that I did was to add a day to the end date. As such
RoundDown(
DateDiff(
DatePicker1.SelectedDate,
DateAdd(
DatePicker2.SelectedDate,
1
),
Days
) / 7,
0
) * 5 + Mod(
5 + Weekday(
DateAdd(
DatePicker2.SelectedDate,
1
)
) - Weekday(DatePicker1.SelectedDate),
5
),
This resolved it. Just a suggestion to anybody who has been in the same shoe. Cheers.
Thanks for pointing this out.
@@RezaDorrani is there any way to find number of days difference between two Dates, Excluding weekends in Power Automate.
Please help
@@badiuddinkhan5430 Check github.com/rdorrani/Microsoft-Flow/blob/master/CalculateWorkingDaysbasedonagivendaterange_20190804164956.zip
Your video is easy to follow along! Congrats and thanks once again!
Awesome, thank you!
I was breaking my head for more than a week over the second use case that you talked about in your video. Wow. This just is amazing. Thank you Reza!
You are most welcome
You’re a true master at teaching this Reza. Great content, many thanks.
Glad you think so!
I have been looking for these formulas, glad I found it, still available … thanks Reza.. as usual great vid ✅✅
Most welcome!
Such helpful videos! I was looking for this exact solution and you made that possible! Thank you as always!
Most welcome
Remarkable and outstanding as always my friend!
Thank You so much
Thank you Reza! It helps me to sort out date difference very fast.
Great to hear!
Thank you so much you're really a life saver!
Glad to hear that!
Thanks Reza, I think you maked PowerApp so much easy for learn ❤️
You're welcome 😊
Hi Reza, thanks for the great explanation. This makes it easy for a beginner. I wanted to find out how to include half day calculations, assuming I applied for half day leave instead of a full day…?
Thanks.
I have done this video long time ago. You will need to look at the formulas put in play and manipulate it for half day logic.
I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Thank so much!!!! I'm very grateful for your help, it helped me a lot.
Most welcome and thanks for watching
Hi thanks for such a short and precise explanation where it is much simpler than many others.
Just wonder, RemoveIf(DS, to exclede weekday), it is pretty straightforward. Just curious, how RemoveIf can detect CountRows(Filter..holiday) > 0? Wish to know how does it really work out though it looks simple.
TQ
Sorry but I did not understand the question.
I can see u r using Countrows to filter out from Removeif. I saw the same in Microsoft sample document.. May I know the logic behind?
@@hpkeong That checks to see if the date falls in the holidays collection range. If it does then remove it. To check if it does, Im using CountRows(filter condition) > 0
Basically if that date does exist in that holiday range then countrows will return a number greater than 0
New to PowerApps. Thank you for this wonderful tutorial. I've applied your logic to two date pickers where if a particular date is selected on datepicker 1, then it would dynamically populate datepicker 2 (in view mode). Question please. How would you adjust this logic where onchange of datepicker 1, it would dynamically populate datepicker 2, 3 and 4 on the same form with different dates? I tried passing differing number of records in Last(FirstN( colDateRange[,varNumberOfRecords ] )).Date, in each datepicker, but each time Last pulls the last record of colDateRange. Would I need to create a different collection? For example, datepicker 2 shows values: Last(FirstN( 121records,121 )).Date which pulls the 121st record, datepicker 3 I have Last(FirstN( 121records,108 )).Date, but it pulls the 121st date instead of the 108th date. Appreciate any direction.
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani Thank you, I will try that. I tested using a label and when I hard code the second value that passes the number of records to return, I see now that it is returning the indexed row excluding the omitted holidays. I'll watch your video again as I must have missed a step. So it's working, it's just returning a null value because the number of records I am passing is greater than the data set because the holidays and weekends have been omitted. So the last record it returns is indeed the last record of the entire dataset, not the date that corresponds to the "RowIndex" value.
Thanks Reza your simply awesome.
Thank You 🙏
Thanks, such a precious video.
Glad you enjoyed it!
Great video. I know it's a few years old, but is there a way of doing the holidays lookup when the formula is being created within a table column within a model-driven app? I have UK bank holidays listed in a separate table within my solution, but I can't get the formula to find it when I try to reference it as part of the calculation. Thanks in advance!
No sure about MDA
Reza you are a god, Thank you !!!!
Thanks man but no God.
A simple content creator.
Hello Reza Thank you for educating us on the nuances of Power automation - I need your help with a quick query I am using a SharePoint list where I have start and end dates below are the things that I wish to achieve
1. Calculate the difference between the two dates that includes the start date and excludes weekends and holidays if any
2. There is no holiday column in my list - how to exclude those holidays
This video is on Power Apps not Power Automate.
I do not have a video reference on your scenario. I have seen plenty of solutions on forums related to this.
I will recommend checking or posting your query on forums at powerusers.microsoft.com
@RezaDorrani
Hey Reza, love your content! Just wanted to let you know that your code isn't working for a specific test case. If my start date is on Sunday(Weekday Value = 1) and end date is on Friday(Weekday Value = 6) then the difference is 5. So (5 + 5) Mod 5 = 0. Then it gives value as 0 instead of 4.
Thanks.
This video was done a long while ago, hence do not remember the exact code and outline.
I may have missed some scenario.
Hello Reza,
This was really helpful! Thank you!
A quick question - how do you take into consideration business hours, say 8am-5pm so that after COB the tickets are placed on hold and would continue reading next day upon Start of Business (8am)?
I would need to do a separate video on that topic and would need to try it out myself to know the steps. I would recommend checking on forums in case someone has done something similar
powerusers.microsoft.com
Excellent! I always enjoy watching video. Was very much helpful. I'm looking for something like to disable all days and enable only a particular day in a week. Can we achieve that? Thanks in advance!
I believe there is a start date like property in calendar control. It will allow users to pick from that date onwards.
Alternatively, you can always let user select any date and on submit validate the date and accordingly display error message.
Thanks for this video!!
My pleasure!
Hi... thanks for this Informative video. Just one query if we wanted to add days to a date in a date picker rather than today how to implement it?
You can use the function DateAdd to add days.
Thanks for another great vid. How could I check holidays for the next 30 days instead of current year? At the end of year there are holidays each side of 12/31
Thanks.
Its been a long time since I did this video and do not recollect the formulas. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, could you pls make a video on how to calculate estimated end time for each task, if we have start time and duration in hours as an input and then exclude weekends & Holidays?
I make videos when I receive multiple requests on a topic. This is the first request on this topic. I have video ideas in my backlog with over 50 requests. Il recommend checking on forums in case someone has done something similar powerusers.microsoft.com
This has been really helpful and so easy to follow. A challenge I am having, however, is reverse engineering it so I can find the "start date." For example, I have the date for which a project is due and based on the known X amount of days a task takes, I need to find when the team needs to start the task. So, instead of
KnownStartDate+KnownDuration(excluding weekends/holidays) =Due Date, I need
KnownDue Date - Duration(excluding weekends/holidays)=Start Date. Is this something you know how to do?
I have your solution. I will share the code base.
Send me an email on reza.dorrani@outlook.com.
@@RezaDorrani Hi Reza. Could you also provide me with this solution if I send you an email? Many thanks.
@@Quincunx5 email me at reza.dorrani@outlook.com
Greetings, I love this! However, I was wondering if there was a way to have a different day then Today()? For example a Date Field in the Form that Changed based upon an entry. Then the future date would be determined using this method and the user inputted date. For example:
User Input: 8/21/2023
DropDown: 84 (days)
Output: Future Date (minus weekends and holidays)
I have not tried this scenario but I guess it should be possible. I would recommend posting your query on forums in case someone has done something similar powerusers.microsoft.com
@@RezaDorrani Hi Reza, first off, thank you so much for your invaluable help with all things PowerApps! ❤
I struggle too with the concept of replacing Today() with a Date Field from a sharepoint list.
Is that something you can help us with or point us in the right direction please?
Again, thanks a million!!
@@Kinethik Thanks so much.
I have not tried this scenario hence not sure what the steps are.
I would recommend checking or posting your query on forums in case someone has done something similar
powerusers.microsoft.com
Thank you! How do you create dates in Power Apps configured to your data source, rather than using the default date picker. Calculates the number of dates: Submission dates minus Approved dates.
Not sure if I understood your question. If you use the form control, the date controls would be linked to the SharePoint list.
Hi! Thanks for this, it helps. One small "issue", for example if I import list of Holidays to the table then some of them may occur on the weekend (eg. Independence Day on Sunday). So this algorithm may substract one day twice, once as weekend, once as Holiday, right? I have to exclude Holidays that occur on the weekend from the list or from the algorithm.
You would need to update code to accordingly remove the holidays that fall on weekends. I would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi dominika were you able to find formula ??
Thank you Reza!
You are most welcome!
Nice intro! 👌
Thank you. Shot the intro at the golf course :)
Hey Reza, Great work
I was able to learn just because of your videos.
In excluding weekends I have a doubt,
For getting the exact dates say need different from 6/Feb/2023 to 8/Feb/2023 according to your video we will get only 2days but it should be 3days. So for that I have added +1 to the end date datacard.
Here has the issue, when I select Monday as start date and end date as Friday of same week. I'm getting 0 as value instead of 5. How can we resolve this?
It’s been a while since I did this video. I would have to try it out and to see what could the fix be. I recommend posting the issue with screenshots on the forums at powerusers.microsoft.com
Thanks for the video! Could you tell how to edit the weekdays in the calendar? Because we have weekends on Friday and Saturday
You can define the start of your work week in the Weekday function - docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datetime-parts#description
@@RezaDorrani Thank you!!
Hello Reza, first of all I want to thank you for all your contribution! it's great. I'm power apps beginner, and wanted to know if there's a solution to a simmilar case in which we need to add 2 workdays based in a date picker card instead of a label item?
Thanks for liking my videos.
Check powerusers.microsoft.com/t5/Building-Power-Apps/Adding-working-days-and-hours-to-a-date/td-p/98747
Is this your debut? Love it 😊
Yes my debut :)
Thank for this Reza, question, what if the box is inside the form? How can I formulate the difference?
Which box? I would have to try it out to provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
This was a brilliant guide on how to compute business days. I just have a problem, the collection is not automatically running when published. what should be the problem?
Thanks!
I have not come experienced this issue and hence not sure what the cause for it could be.
@@RezaDorrani I am also trying to figure it out. It works perfectly when I manually clicked Run app Onstart but when it is published, it is not computing.
@@JomarieVillanueva-t4k I will recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com
Thanks again Sir Reza, do you have some sample formula where it identify conflicting dates, for example, in reservation or leave application, user applied from january 1 to 5 but there is someone already filed from Jan 2 to 4 , appreciate your reply thanks again. :)
I do not have a video reference on this scenario. Idea would be to query datasource to check if any item exists with same dates and accordingly display error message when user tries submitting the item.
I will recommend checking on forums in case someone has done something similar
powerusers.microsoft.com
Hi Reza, this has been really helpful and so easy to follow. A struggling I am having is that i have differents requests and each one have different days to reply, so, i have today date (TodayDate) and based on the days of each request, i have to calcute the date response (DateResponse). So, i need your help please. For example, i have a request 1 and this have a 3 business days for response, so the first calculation is DateAdd(TodayDate, BusinessDays,Days ) but i dont know how to use this RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) in the formula before. Because my endDate is previusly calcuted with the first formula. Is this something you know how to do?
Thanks
Thanks for watching this video.
I'm sorry but I did not understand your use case.
It would be much easier to understand with screenshots.
I would recommend to post your issue/query with screenshots on the forums at powerusers.microsoft.com
Very nice video! How is it possible to group your calculation by Year(date) && Month(date), so you can sum it up in a gallery or chart? I have tried a million different ways, but can't figure out how to input the logic. Either I end up with wrong result or the result is the total days as when using the calculation on label. Very hope you can help ! Thanks
I am not sure I understood the use case. It would probably be easier to see a few screenshots of what you are attempting.
Best option would be to post your query on the Power Apps forum - powerusers.microsoft.com/t5/Building-Power-Apps/bd-p/PowerAppsForum1
@@RezaDorrani Thanks Reza. I already tried Power Users forum, but I did not receive any response.
However I think perhaps I figured out how I can get to the result.
Screenshot of example:
ibb.co/8d9C6XK
Description:
We have around 800 people who are being assigned to our different projects with a startdate, enddate, country, allocation and daily rate. Each month we have a financial status where we compare the actual cost and estimated cost - example February 2021. To be able to do that I need the business days for each assigned person split in to month over month. By multiplying this with a daily rate result in total estimated cost for the particular month.
Formulas:
To your colDateRange I added two columns.
- First Date of month
- Last Date of month.
Filtered out/removed Dates, which are public holidays as pr. your examples in video.
Then added below to a gallery:
Filter(
AddColumns(
GroupBy(
colDateRange;
"Firstofmonth";
"Endofmonth";
"TheItems"
);
"BusinessDaysByMonth";
CountIf(
TheItems;
Date >= Firstofmonth;
Date
Hi Reza,
I have a question on the date.
I managed to create the date box but the placeholder date shows 1/1/2001. Would I be able to use Today's date for the placeholder?
Yes. Just got to properties and replace it with Today() function.
Hi Reza, is it possible to calculate priority in Hours rather than Days?
Yes can be done by adding logic to handle the time aspect as well
Reza Dorrani you got any examples?
First of all I would like to thank the video that solved a problem for me.
Now I have another problem that I don't even know how to explain but here it goes.
I want to limit the time of a registration.
If the date/time selected in the "datepicker" is greater than a certain time, it will no longer allow registration for the following day but it would be possible to do it for the remaining days and it would give an error message "It is no longer possible to register for the chosen day". Can you help
thanks in advance
Thanks for liking the video.
I will have to try out your scenario to provide any guidance. I do not have any video reference for it. I would recommend to post your query on the forums at powerusers.microsoft.com
@rezadorrani, How we can calculate no. of holidays approved till today?
I would have to try it out to know the steps. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Brilliant!
Thank you Kim
thanks much sir pogi
Welcome
Hi Reza... I have hunted through the videos you have on date related stuff, however cannot find an answer to an odd problem. I have a form to build and I was using set() to assign variables for values as they stay put when moving into other screens etc (collections were problematic with patch). I have a field which is a text field that is prefilled with text(today(),"dd/mm/yyyy"). It is captured in the variable fine. However when I get to the final review form which uses all the created variables to populate the fields (which are then posted to Sharepoint), every field is happy to be assigned a variable except the date field. I have tried changing the Sharepoint field to single line text and as a date field. This hasnt made saving possible. When the final form is loaded the default field for the job date is a date/time field. I can change the datacard value to the set variable, but instead of showing it as the date in the variable it shows as a different date for reason. (It's a date but its about 12 months ahead of the actual date ie 21/05/2022 becomes 9/5/2023 which is bizarre). It's a big ask, but do you have any idea how to resolve. I did use your video on the photo converted to a sharepoint attachment which works perfectly, so the form updating the SP list actually has 2 data sources that feed it. They all work except the date for some reason. (Technically I can dump the field and use the "created" field instead if there's no workaround. Thanks. Tony
Best I can do on chat is guide. I cannot know about your issue without looking at your App in action. My guess is something to do with the data card and the way your are settings its default value or update property.
Date going 12 months ahead sounds weird.
I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani I will do that... I have created a work around by simply setting the form date to the current date so that I don't need to use the variable. This is more of a problem of why did 99% work but not this one thing.
How did you change the color scheme of the date picker? I can only change the Icon colors.
I believe it follows the theme colors of the App.
Thank you Reza
You are most welcome
Finally , I found what I was looking for. Yey! But I'm getting a lot of errors in the beggining of the formula where the collections are created...😢
I will have to look at your App in action to know why you are getting all those errors :)
I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
I tried again and it worked eventually. I've started working with Powerapps and still get a little confused with separators because the app isn't in English. Thanks very much for sharing.
@@patricia294 Awesome!
Hi Reza, Please assist, I have a helpdesk app that needs to escalate ticket to a manager if the ticket is not resolved within an SLA of two days. I am using a sharepoint list to store my tickets data. Any idea as how can I calculate this and able to send an email.
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
@@RezaDorraniThank you so much for the reply and the suggestion. I will check the forums.
What do you do when you are starting say Dec 26 and you are going 14 days out. It is looking at the current year, but New Years Day is in the next year? Thanks.
You would need to calculate and add logic for the year. I believe there is a Year based Power Fx formula as well.
Notice that the partial week calculation given above will not work if the start or end dates fall on weekends; in this case we’d need to decide what to do (either use the previous Friday or the following Monday) in the calculations - and that will depend on the application.
+1
@@RezaDorrani Thank you , Appreciate your effort and sharings
Can you suppress the related video popup near the end? It's covering the bottom 1/4 of the window.
Made the update. Let me know if still an issue.
Dear Reza,
I am tuck in very strange error while using datepicker,
i have 2 containers on screen,i am using datepicker in right container.
when i run app, and click on datepicker. it expands the datepicker in top left corner of screen..
i need your help on this
Not sure about this one. I will have to look at your App in action to provide any guidance. I will recommend you post your issue with screenshots on the forums at powerusers.microsoft.com
Hi Reza,
Is there a way I can get those week dates too ??
Possible by updating the code logic to include weekdays.
@@RezaDorrani The scenario is I have 2 date pickers. I am suppose to collect the dates between those.
But the weekends should be excluded. How do i do it ??
@@aafreensultana1835 I would have to try it out to let you know how. I will recommend posting your scenario on the forums at powerusers.microsoft.com
Hi, i applyed the leave days calculation to my form and it looks perfect, however it will be calculating in days in minus if start date is after end date, i tryed with wrror message validation but it misses up my calculation
Validation would be the way. I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Sir, I would like to ask how to filter the skipped/missing dates in the sharepoint list using powerapps?
Not sure what you mean by missing dates. It all depends upon the filter query. I have not tried something with skipped dates, so not sure.
@@RezaDorrani Hi Sir, I have a column in sharepoint list that has submitted dates of every employee for there activity monitoring and the problem is how can I filter/collect all the skipped submit dates of employees. This is the first time I tried this also.
Ex.: Submitted Dates = 05/01/2022, 05/02/2022, 05/05/2022
"Problem collect the skipped dates"
Skipped dates = 05/03/2022, 05/04/2022
@@jumilbolhano3910 I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani Hi Sir, Thank you
I would like to know if I can put criteria to add 2.5 leave per month to the current leave balance. Let's say if I'm applying leave for after 3 months, my current leave balance should also add 7.5 leave days for the coming 3 months.
Can be done. You would have to get the current leave balance from your systtem of record and add 2.5 days per month to your leave balance. This would need to be done on a monthly basis by an automated job. Power Automate (flow) is your answer.
Do you have a link youtube video or article for it. :)
Can we calculate business days with power automate for sharepoint list ? if so can you show us how
Possible but I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
What if you wanted to subtract dates to create reminders?
For that it is best to use power automate
Hi reza! Just a quick question.. why i add “Days” my formula become error ?
No idea as I have not experienced this issue. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hi @amalgaga5378 I ran into the same problem. You have to write the formula as "TimeUnit.Days" and it should work.
Can we achieve the same using PowerAutomate/FLOW? As I'm not much familiar with power apps.
Yes. You would need to use expressions for it - aka.ms/learnexpressions
Hello Rezza, Great video as usual, Passing date into Flow as parameter throwing error for SharePoint date column.
Code - Text(DataCardValue13.SelectedDate,"[$-en-US]mm/dd/yyyy","en-US"), ----
Error - string 'mm/dd/yyyy/' does not validate against format date, Any thoughts on this..?
In flow I've used formatdatetime. But same result.
Appreciate your reply on this. Thank you.
This video does not use flow to pass params.
However, make sure you are passing the correct parameter to flow from Power App based on data type defined in flow.
You can just send selecteddate. No need for parsing and use it directly in flow. Both flow and Power App work with UTC.
@@RezaDorrani Thank you for your reply.Yes, as the video is on Date calculation, thought to check with you.
Text(DataCardValue13.SelectedDate, "[$-en-US]yyyy-mm-dd"),
worked for me. Initially was not working.
Thank you once again..
Is there any way to find number of days difference between two Dates, Excluding weekends in Power Automate.
Check github.com/rdorrani/Microsoft-Flow/blob/master/CalculateWorkingDaysbasedonagivendaterange_20190804164956.zip
Import this flow and see the code.
I am doing a gantt chart in days. how to calculate the x and width value? i still doing it a month already. but still cannot. i dont want to use powerbi. can u teach me? please
Hi Mohammad,
I did not understand your question clearly.
You can post your question in detail alongwith screenshots on the community forum - powerusers.microsoft.com/t5/General-Discussion/bd-p/PowerAppsForum1
hii Reza hope u r doing well i jusnt want to know i m taking an drop down and i want to show today +5 days form today means a whole week but i want when ever a user select the drop down He/She can only see the week days like for ex: todays date 1 and till 7 there is week off on 6 and 7 like Saturday and Sunday so now i want the drop down have to except the 6 and 7 and shows the 8 and 9 which is next weeks monday and tuesday please help me in this
I would have to try your scenario out to know the steps and provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
thanks@@RezaDorrani
😍
👍
I have delegation warning when placing dates in collector
How can I remove that message?
I did not experience any warnings hence not sure.
I will recommend posting your query on forums at powerusers.microsoft.com/
how did you change the date(calender) colour.
By selecting the purple theme for the App.
Thanks@@RezaDorranifor the reply, how can we custom change the date(calendar) colour. is it possible?
@@quratulainmansoor8776 Not everything can be updated. There are many limitations with the date control. There are some samples of custom date controls on the forums that allow modifying the colors. Check the forums powerusers.microsoft.com/
@@RezaDorrani JazakAllah khair.
You are contributing great work. Thank you for your time and effort.
@@quratulainmansoor8776 Your most welcome
Hi Reza
How can I display a time in a canvas app based on Internet Time or Server Time. (Which does not use the API)
The problem is when I used the Function Now() it displays the user's local time in his/her PC. So everytime the user change their time, the Time in Canvas app will also change.
I am creating an Attendance Tracker I don't want the user's to cheat in their Time-in or Time-out by changing their device or pc Time.
I hope anyone can help me. Thank you.
Im not sure about this one. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
The Second query Is not Working, I'm Getting an error like { The function 'Clear' has some invalid arguments. Name isn't valid, 'colDateRange' isn't recognized......Clear(colDateRange);
I have not come across this issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Reza,
How to get Quarter next to Date . Once date is selected
powerusers.microsoft.com/t5/Building-Power-Apps/Calculate-first-date-of-next-quarter/td-p/643731
@@RezaDorrani Thank you so much for replying. Need just to get Quarter from date. 24-Feb-2021 ... =QUARTER(24-Feb-2021)
👍
Thank you
how can we create last 12 rolling quater ?
Not sure about that one. I would recommend to post your query on the forums at powerusers.microsoft.com
Hi Reza,
I need your help big time and hoping you would help as I couldn't find any solution by googling.
Currently, I'm implementing event form in which I need to calculate total hours between 2 dates, time and minutes. If date is same then calculate hours using Time and Minute otherwise calculate hours based on Date, hours and minutes between 2 dates.
eg:- Start Date: 04/24/23, Hour: 8 AM, Minute: 30 and End Date: 04/24/23, Hour: 12 PM, Minute: 30 should return 4 hours otherwise number of hours divided by 8 as 8 hours is the longest event time per day. Could you help me with formula?
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking or posting your query on the forums at powerusers.microsoft.com/
Hello!
How to find the number of hours worked by employee ABCD knowing that we have a table that looks like this:
Employee || Date ||Start time ||End time
******************
ABCD || 01/01/2022 || 08:00 || 11:01
ABCD || 01/01/2022 || 12:05 || 15:00
ABCD || 01/01/2022 || 15:55 || 18:16
Please help me.
I do not have a video reference on this scenario and will have to try it out to provide guidance. I recommend posting your query with screenshots on the forums at powerusers.microsoft.com
Hello, Can you help me with the sharepoint
If you have any SharePoint related issues/queries - post on the forums at techcommunity.microsoft.com/t5/sharepoint/ct-p/SharePoint
If you have any power platform related issues/queries - post on the forums at powerusers.microsoft.com/
This video is very helpful. Unfortunately i am not getting the holidays and weekends removed. Any idea where i could go wrong? I just hardcoded 30. Here is my collection formula
ClearCollect(
collHolidays,
Filter(
Holidays,
Year(HolidayDate) = Year(Now())
)
);
Clear(collDateRange);
ForAll(
[
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
26,
27,
28,
29,
30
],
Collect(
collDateRange,
{
RowIndex: CountRows(collDateRange) + 1,
Date: DateAdd(
Today(),
Value
)
}
)
);
RemoveIf(
collDateRange,
Weekday(Date) = 1 || Weekday(Date) = 7 || CountRows(
Filter(
collHolidays,
HolidayDate >= Date,
HolidayDate 0
);
ALright Nevermind. Figured it out. There is some formatting issue. Okay i can sleep now :)
I can sleep now too :)
I was about to go try out and check to see why it did not work for you.
Glad you figured it out!
@@vinayayinapurapu what was the formatting issue? i can't figure out my error. my coding removes the weekends but won't remove the holidays. colHolidays and colDateRange collect the data correctly, but RemoveIf returns a null set when i add the CountRows Filter
@@angelaess326 did you try copying code from the Github and pasting in notepad and then copying to the PowerApps function?
hi i followed this video for my due date. it works perfectly in new item, but when i re open the form let say next day, the due date change
You need to add logic in form so that due date does not change for items already saved. You can check form mode to define that.
Can you show me an example or video?
Hi Reza..
I have a sharepoint for issue tracker for my team.. All I want is to create a date column which will be a calculated field based on created date. (That will exclude weekends)
If created date is 1st january then due date will be 1st jan+ 2 days = 3rd january.
Incase 1st Jan is on Friday then due date will be 5th January ( excluding Sat and Sun day).
Please help me to create this column in sharepoint.
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
@@RezaDorrani I posted and no one responded yet
@@thewanderingcouple8378 Forums is a free platform. You may or may not get a timely response. If this is time sensitive, then you need to look at getting consulting help.
How to use 360 days function in powerapps?
I am not sure I understood the question.
Hi there, this is very userful topic. However if the user raise a critical priority ticket at EOD, the due date must be shifted to next day {because critical ticket to be addressed on the same day). Is that possible? Say for example, if the business hours is between 9 AM to 5 PM, and user raise a high priority ticket at 4 PM, the due date must be shift to next day. Please guide
Possible. In case of time calculations the formulas would get a lot more complex though.
I do not have a video or use case on this specific scenario.
Forums will be your best bet - powerusers.microsoft.com/
how to calculate a half day
Not sure about that one. Something for me to try out.
Il recommend checking on the forums at powerusers.microsoft.com
May I Know how to exclude Sunday only
Thank you everyone who can help me first
The formula checks day of week to get Saturday and Sunday. Just update formula to remove the Saturday logic.
For 5 days difference ,it is showing 0. Can anyone try and fix this error
I did not get this issue. I will recommend to post your issue with screenshots on the forum at powerusers.microsoft.com/t5/Building-Power-Apps/bd-p/PowerAppsForum1
@@RezaDorrani , My issue is , difference of start date (already there in the records) and Today() is showing some wrong value when Today() day is weekend, otherwise it is working correctly . For example if start date is 22-march-2021(Monday) and Today () is 27-march-2021(Saturday), the actual difference value is 4, but is showing 0.How to overcome this situation?
@@RezaDorrani I have the same problem
Just an update. Instead of hardcoding arrays from [1......*] Just use Sequence(YourDesiredNumber)
Instead of:
Clear(colDateRange);
ForAll(
[
1,
...
39,
40
],
Collect(
colDateRange,
{
RowIndex: CountRows(colDateRange) + 1,
Date: DateAdd(
Today(),
Value
)
}
)
);
Use:
Clear(colDateRange);
ForAll(
Sequence(40),
Collect(
colDateRange,
{
RowIndex: CountRows(colDateRange) + 1,
Date: DateAdd(
Today(),
Value
)
}
)
);
I don't think sequence was available when video was recorded. But surely, sequence is a way better option.