Datediff excluding weekends and public holidays

(0) Share

Report

Posted on by Community Power Pla...

Hi how to count the days from a date picker from and date picker to excluding weekends and public holiday?

I assume that I need to create a list of all public holidays.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

v-yamao-msft on at

Like (2)

Report

Hi harvinb,

I have seen a similar thread about this issue, the thread link is:
https://powerusers.microsoft.com/t5/PowerApps-Forum/Excluding-weekends-using-DateDiff-and-Datepicker/td-p/38122

Please try to function provided in the thread to see if it will work for you.

Please feel free reply if you need more help.

Best regards,
Mabel Mao

Was this reply helpful? Yes No
Verified answer

CarlosFigueira on at

Like (3)

Report

Yes, you'll need to create a list of holidays - as this value is different not only between countries, but also between regions (states, cities, etc.) within a single country.

The post at https://blogs.msdn.microsoft.com/carlosfigueira/2017/09/23/excluding-weekends-and-holidays-in-date-differences-in-powerapps/ shows how you can exclude weekends and holidays when calculating the difference between two dates.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

Below solution works for me.

(Switch(Mod(StartDate.SelectedDate - Date (1985,6,24),7),
0, Switch( Mod( EndDate.SelectedDate-StartDate.SelectedDate,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, Switch( Mod( EndDate.SelectedDate-StartDate.SelectedDate,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, Switch( Mod( EndDate.SelectedDate-StartDate.SelectedDate,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, Switch( Mod( EndDate.SelectedDate-StartDate.SelectedDate,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, Switch( Mod( EndDate.SelectedDate-StartDate.SelectedDate,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, Switch( Mod( EndDate.SelectedDate-StartDate.SelectedDate,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, Switch( Mod( EndDate.SelectedDate-StartDate.SelectedDate,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ RoundDown(((EndDate.SelectedDate-StartDate.SelectedDate)/7),0)*5
- Sum(Filter('Public Holidays', Date >= StartDate.SelectedDate And Date <= EndDate.SelectedDate),No_x0020_of_x0020_holiday))/If(HalfDay.Value = true,2,1)

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thank you for your help with this post @CarlosFigueira, @v-yamao-msft and @Anonymous!

@Anonymous

Was this reply helpful? Yes No
paulvanstraaten 3 on at

Like (0)

Report

There is a day missing when using this method.

Was this reply helpful? Yes No
BenVdP 79 on at

Like (0)

Report

Hi @CarlosFigueira,
Sorry to bump this thread, but I'm wondering if there is also a solution if I want to be able to work with hour differential instead of days? I tried to come up with some solution, but I can't figure it out, especially when I need to take in account weekends and holidays as well.

Was this reply helpful? Yes No