cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vinc1992
Helper I
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

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
)

View solution in original post

3 REPLIES 3
WarrenBelz
Super User
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).

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

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
)

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,497)