cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harvinb
Level: Powered On

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
PowerApps Staff CarlosFigueira
PowerApps Staff

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

4 REPLIES 4
Community Support Team
Community Support Team

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.
PowerApps Staff CarlosFigueira
PowerApps Staff

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

powerapps_ps
Level: Powered On

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)

Administrator
Administrator

Re: Datediff excluding weekends and public holidays

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

 

@TopShelf-MSFT

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 152 members 3,198 guests
Please welcome our newest community members: