Answered

DateDiff exclude weekend

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,

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).
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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
)