Solved: Re: DateDiff exclude weekend

Anonymous · ‎04-23-2022

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

Anonymous · ‎04-23-2022

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

WarrenBelz · ‎04-23-2022

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).

Anonymous · ‎04-23-2022

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

Anonymous · ‎04-23-2022

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
)