cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
E-Mac
Level: Powered On

Excluding weekends using DateDiff and Datepicker

Hi All,

 

I have been using DateDiff to calculate the number of days between two dates using the datepicker.

Is it possible to do this but exclude weekends (Saturday & Sunday)?

 

eg. Friday - Monday currently counts as 4. I would like this to count as 2

 

Current Code: DateDiff(DateValue(Text(StartDate)), DateValue(Text(EndDate)))+1

 

Any help would be great, thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Excluding weekends using DateDiff and Datepicker

You can use a combination of the DateDiff function with the RoundDown function (to calculate the number of weeks between the two dates) and a combination of the Mod function and Weekday function (to calculate the number of weekdays between the dates):

 

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
    Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5)

The first line calculates the number of days in whole weeks between the two dates; the second line calculates the number of days in the "partial week" that is remaining. This formula will work well if the EndDate is after (or the same as) StartDate; if this is not the case, then the formula will likely need to be tweaked to account for negative differences.

 

View solution in original post

21 REPLIES 21
Highlighted
Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Excluding weekends using DateDiff and Datepicker

You can use a combination of the DateDiff function with the RoundDown function (to calculate the number of weeks between the two dates) and a combination of the Mod function and Weekday function (to calculate the number of weekdays between the dates):

 

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
    Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5)

The first line calculates the number of days in whole weeks between the two dates; the second line calculates the number of days in the "partial week" that is remaining. This formula will work well if the EndDate is after (or the same as) StartDate; if this is not the case, then the formula will likely need to be tweaked to account for negative differences.

 

View solution in original post

E-Mac
Level: Powered On

Re: Excluding weekends using DateDiff and Datepicker

Thank you, worked great!

sscarcella
Level 8

Re: Excluding weekends using DateDiff and Datepicker

When I use this formula with my two date picker fields I get invalid use of "." for both DateDiff and Weekday. 

Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Excluding weekends using DateDiff and Datepicker

Make sure that the names behind the '.' are the names of the date picker controls. If this is not your problem, can you post a picture of the error where it is showing the error?

sscarcella
Level 8

Re: Excluding weekends using DateDiff and Datepicker

error1.PNG

Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Excluding weekends using DateDiff and Datepicker

That formula doesn't look to have errors (there's no squiggly line between the expression in the formula bar); can you open the Advanced pane (right side), and show all properties of that control? The one with the error should have the yellow triangle beside it.

sscarcella
Level 8

Re: Excluding weekends using DateDiff and Datepicker

error2.PNG

Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Excluding weekends using DateDiff and Datepicker

That's strange - whenever there's an error, we usually see squiggly lines on the editor.

 

Anyway, I believe I've spotted the error. Begin_x0020_Date and End_x0020_Date are the name of the cards, not the name of the date picker control. The control names are called DataCardValueXXX (where XXX is a number). If you select the control inside the card in the tree view you'll see the name that you need to use.

 

Anonymous
Not applicable

Re: Excluding weekends using DateDiff and Datepicker

Hi,

I tried the formula and it works great except if you choose StartDate on Monday and EndDate on Saturday/Sunday, the result is 0.

Also, I would like the dates to be inclusive. If StartDate is on Monday and EndDate on Friday, the result to be 5 and not 4. How can I do that, please?

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,177)