07-08-2020 10:52 AM
Hello everyone,
In this video, we will learn about the Power Apps date and time functions & cover 2 scenarios for business days calculations in Power Apps.
Scenario 1: Calculate date difference in business days between 2 dates.
Scenario 2: Add Business days to a given date. Ideal for calculating due date for tasks.
Formula used to get list of dates OnStart function of the Power App https://github.com/rdorrani/PowerApps/blob/master/duedateworkingdays
Reference article: https://powerapps.microsoft.com/en-us/blog/excluding-weekends-and-holidays-in-date-differences-in-po...
--------------------------------------------------------------------------------
Thanks,
Reza Dorrani, MVP
YouTube
Twitter
watch?v=QVvhDe6puq0
Hi,
Many thanks for this in depth tutorial. I just have a few questions that may or may not be possible to do for our form.
1) Our weekday would start on a Monday so I think that is the reason for the following... Lets say I request 01/01/2021 - 03/01/2021 that is 3 days leave. So days requested should be 3, how can I change this to reflect it? I think right now it is taking the EndDate as the date the employee returns to work so is showing up as if the employee has requested 2 days off. Easiest solution for this would be to tell our users to use the end date as the return date, but I think we all know how end users are 🙂
2) Is it possible to work out hours rather than days or to work out half days? The reason I ask is because sometimes employees will take half days, we want to be able to create an annual leave request that can allow for half days or days + half days (e.g. 2.5 days off)
Again, I followed the video completely and this is really the first proper tutorial I have seen that goes through everything - it really has helped me create our holiday request form!
Looking forward to your reply
Kind Regards,
Jamie
Hello Reza,
Thanks so much for your great tutorial, your scenario 2 is exactly what I need!!
The only difference is that I need to calculate a Due Date based on a Date Field (Sent Date), not Today() like in your example.
How can I achieve this?
This is the code I have so far:
Clear(colDateRange);
ForAll(
Sequence(20),
Collect(
colDateRange,
{
RowIndex: CountRows(colDateRange) +1,
Date: DateAdd(
Today(),
Value
)
}
)
);
RemoveIf(
colDateRange,
Weekday(Date)=1 || Weekday(Date) = 7)
Thanks again for your help!