Weekdays between two datesYou may have a need to compute the number of weekdays between two dates. This formula does not include the starting date, but does include the ending date. For example, June 1st to June 2nd is 1 day (the 1st is not include, but the 2nd is). There is probably a more "mathematical" way to compute the difference, but this way should be pretty easy to understand.
Check := @If(@IsTime(StartDate) & @IsTime(EndDate); 0; @Return(0));
S := @If(StartDate < EndDate; StartDate; EndDate);
E := @If(StartDate < EndDate; EndDate; StartDate);
DiffDays := @Integer((E - S) / 86400);
WeeksBetween := @Integer(DiffDays / 7);
WS := @Weekday(S);
WE := @Weekday(E);
Adjust := @If(WS = WE; 0; WS = 1 & WE = 7; 5; WS = 7 & WE = 1; 0; WE = 7 | WE = 1; 6-WS; WS = 7 | WS = 1; WE-1; WE > WS; WE-WS; 5+WE-WS);
(WeeksBetween * 5) + Adjust
How does this work? The first thing that is done is make sure we're dealing with dates. StartDate and EndDate should be your Notes fields that have the date values. If either one isn't a date, then return a value of 0 as the number of weekdays between.
Next, set some temporary variables S and E. These will be the starting and ending dates. This is done so we know that S will be the earlier date and E will be the later date.
Then we figure out how many total days and how many total weeks are between the two dates. 86400 is the number of seconds in one day. Then we get the weekdays of the starting and ending dates. This will make computations in the next line easier.
Next, we figure out if we're going to need to make any adjustments to the number of weekdays. For example, if the starting date and ending date are Mondays, then the number of weekdays is 5 times the number of weeks. But if the starting date is a Monday and the ending date is a Wednesday, the number of weeks times 5 will miss the final Tuesday and Wednesday (Adjust is 2). This is done in the Adjust line.
If the first day is a Sunday and the last day is a Saturday, then the dates between (5 days) weren't covered in a week, so that's the amount of the adjust. If the first day is a Saturday and the last day is a Sunday, the dates were covered in a week, so no adjustment is needed. If the ending date is a weekend (the starting date won't be a weekend, or it would have already been covered), then adjust by 6 minus the starting weekday. This is amount of days needed to get the starting date to a weekend, but not including the starting date. If the starting date is a weekend (the ending date won't be at this point), then adjust the ending weekday minus 1 (the extra 1 is for the Sunday on that final week that shouldn't be counted). If the two dates are not weekend dates, then see which one is greater. If the ending weekday is greater, then it needs to adjust the days between the starting date (number of weeks computed that) and the ending weekday. If the starting weekday is greater, then do the same adjustment, except that one more week needs to be added.
Lastly, take the number of weeks, times 5, and add in the adjustment to get the number of weekdays between the dates.