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. 

 

 

 

 

11 REPLIES 11

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.

andypi
Frequent Visitor

If it is of any interest, I found this to become fairly readable and easy to flip to count only weekends:

CountIf(
    AddColumns(
        Sequence(
            1+DateDiff(
                StartDatePicker.SelectedDate,
                EndDatePicker.SelectedDate,
                Days
            )
            ,Weekday(StartDatePicker.SelectedDate, 3)
        ),
        "weekday",
        !RoundDown(Mod(Value,7)/5,0)
    ), 
    weekday
)

 Not sure about performance hits of this (CountIf), vs Sum vs above conditionals and direct calculations?

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,494)