cancel
Showing results for
Did you mean:

## 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
Resolver I

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.

Announcements

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

#### 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)