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
Solved! Go to Solution.
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
)
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
)
User | Count |
---|---|
260 | |
109 | |
92 | |
56 | |
41 |