cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Datediff excluding weekends and public holidays

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Power Apps
Power Apps

Re: Datediff excluding weekends and public holidays

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.

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Datediff excluding weekends and public holidays

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

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Power Apps
Power Apps

Re: Datediff excluding weekends and public holidays

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.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Datediff excluding weekends and public holidays

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)

Highlighted
Anonymous
Not applicable

Re: Datediff excluding weekends and public holidays

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

 

@Anonymous

Highlighted
Regular Visitor

Re: Datediff excluding weekends and public holidays

There is a day missing when using this method.

Highlighted
Frequent Visitor

Re: Datediff excluding weekends and public holidays

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.

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,767)