Datediff excluding weekends and public holidays

[Anonymous]

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

[v-yamao-msft]

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...

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

[CarlosFigueira · Accepted solution]

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-d... shows how you can exclude weekends and holidays when calculating the difference between two dates.

[Anonymous]

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)

[Anonymous]

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

@Anonymous

[paulvanstraaten]

There is a day missing when using this method.

[BenVdP]

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.