cancel
Showing results for
Did you mean:
Helper I

## DateDiff exclude weekend

Hello everyone,

I use the following formula to calculate the Days between two dates excluding the weekend:

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

But the problem is, that the formula doesnt calculate right. F.e.

StartDate=03.04.2022 and EndDate=09.04.2022

you get the result "2" but the rightone is "5".

Have someone an idea, what can be the problem? It must been use quit a lot as I seen in the forum, so it might be right.

Sincerly Vinc

1 ACCEPTED SOLUTION

Accepted Solutions
Helper I

Found a way:

1+((DateDiff(
StartDate;
EndDate;
Days
))*5-((Weekday(StartDate)-Weekday(EndDate))*2))/7-Switch(
Weekday(EndDate);
7;
1;
0
)-Switch(
Weekday(StartDate);
1;
1;
0
)-CountIf(
Tabelle1;
Date >= StartDate;
Date <= EndDate
)

3 REPLIES 3
Super User

Hi @vinc1992 ,

Congratulations - you have found a bug which seems to affect that 3 and 4 April but nothing else I can see. Try some other dates and you will see the formula is correct (which it is).

Helper I

Puh, I wont use that form it produce to many mistakes.

Other example is, if ur Start.Date is 19.04.2022 and EndDate is 23.04.2022:

Result = 4 days but correct is 3

or 18.04.2022  to 23.04.2022 return "0".

Its not usable!

I try to built sth. else

Helper I

Found a way:

1+((DateDiff(
StartDate;
EndDate;
Days
))*5-((Weekday(StartDate)-Weekday(EndDate))*2))/7-Switch(
Weekday(EndDate);
7;
1;
0
)-Switch(
Weekday(StartDate);
1;
1;
0
)-CountIf(
Tabelle1;
Date >= StartDate;
Date <= EndDate
)

Announcements

#### Power Apps News & Announcements

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

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

Check out the latest Community Blog from the community!

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