- PowerApps Community
- News & Announcements
- News & Announcements
- General
- General Discussion
- Common Data Services
- Common Data Service for Apps
- Best Practices
- Administering PowerApps
- Creating Apps
- Expressions and Formulas
- Galleries
- Community Apps Gallery
- Video Gallery
- Live Event Recordings
- Ideas
- PowerApps Ideas
- User Groups
- Professional Connections & Networking
- Community Blog
- PowerApps Community Blog

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- PowerApps Community
- :
- Forums
- :
- General
- :
- General Discussion
- :
- Excluding weekends using DateDiff and Datepicker

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

E-Mac

Level: Powered On

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-31-2017
08:25 AM

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.

Solved! Go to Solution.

Accepted Solutions

CarlosFigueira

PowerApps Staff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-31-2017
10:17 AM

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.

All Replies

CarlosFigueira

PowerApps Staff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-31-2017
10:17 AM

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.

E-Mac

Level: Powered On

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-01-2017
01:12 AM

Thank you, worked great!

sscarcella

Level: Powered On

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2017
11:01 AM

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

CarlosFigueira

PowerApps Staff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2017
11:14 AM

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: Powered On

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2017
11:20 AM

CarlosFigueira

PowerApps Staff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2017
11:27 AM

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: Powered On

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2017
11:36 AM

CarlosFigueira

PowerApps Staff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2017
01:18 PM

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.

Besiana

Level: Powered On

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2018
01:32 AM

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?

The Power platform Community is Powered by Lithium