cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Li
Advocate I
Advocate I

Share - calculate working days between two given dates

Hello, I was trying to find the method to return the number of working days between two given dates and found the following article

 

https://powerapps.microsoft.com/en-us/blog/excluding-weekends-and-holidays-in-date-differences-in-po...

 

However, as the author mentioned "Notice that the partial week calculation given above will not work if the start or end dates fall on weekends"

 

So I spent some time writing the following formula which seems work for weekends too:

 

If(IsNewRequestFormValid, RoundDown(DateDiff(DatePickerFrom.SelectedDate, DatePickerTo.SelectedDate, Days) / 7, 0) * 5 +
If(Weekday(DatePickerTo.SelectedDate) >= Weekday(DatePickerFrom.SelectedDate),
If(Weekday(DatePickerTo.SelectedDate) = Weekday(DatePickerFrom.SelectedDate) && (Weekday(DatePickerTo.SelectedDate) = 1 || Weekday(DatePickerTo.SelectedDate) = 7),
0,
If(RoundDown((Weekday(DatePickerTo.SelectedDate) - Weekday(DatePickerFrom.SelectedDate))/5,0) >= 1,
5,
If(Weekday(DatePickerTo.SelectedDate) = 7,
Weekday(DatePickerTo.SelectedDate) - Weekday(DatePickerFrom.SelectedDate),
If(Weekday(DatePickerFrom.SelectedDate) = 1,
Weekday(DatePickerTo.SelectedDate) - Weekday(DatePickerFrom.SelectedDate),
Mod(Weekday(DatePickerTo.SelectedDate) - Weekday(DatePickerFrom.SelectedDate), 5) + 1)))),
(7 - Weekday(DatePickerFrom.SelectedDate) + Weekday(DatePickerTo.SelectedDate) - 1)))

 

I am sure it can be improved, so please feel free to make any enhancement. 

 

 

 

 

10 REPLIES 10

Hi Ipr. I just read this article and also thought about this. I would say that one could set a variable on the OnSelect property of the form's submit button which sets a true or false value for all fields being validated e.g.

 

Set( IsNewRequestFormValid, !IsBlank(Name) && !IsBlank(Department) && !IsBlank(ContactNumber) && !IsBlank(StartDate) && !IsBlank(EndDate) )

 

The IsNewRequestFormValid variable would set to true if all fields are not blank. Then using "If(IsNewRequestFormValid,True,False)" would then return the true value for the if statement.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (5,172)