cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
CarlosFigueira
Power Apps
Power Apps

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
v-yamao-msft
Community Support
Community Support

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.
CarlosFigueira
Power Apps
Power Apps

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
Not applicable

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
Not applicable

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

 

@Anonymous

There is a day missing when using this method.

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
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,273)