Adjust By "x" Business DaysI'm sure you've seen code on Notes Net or elsewhere to take a date and adjust by a fixed number of business days. The code usually looks at the current day of the week and adjusts by the fixed number plus some offset. For example, if you want to adjust by 2 business days then if the starting date is Monday through Wednesday you adjust by 2 days, Thursday through Sunday is adjusted by 4 days.
This code will adjust by "x" business days where "x" can be any positive number. The whole key to adjusting by an unknown number of business days is to figure out how many whole business weeks you want to adjust. After the whole business weeks are adjusted, you may or may not end up with a certain number of days left to adjust (anywhere from 0 to 4 since there are 5 business days in a week). Then you can use something similar to the static formula to figure out how much more to adjust.
Note that this formula does not account for holidays - that would add quite a bit to the complexity. Also note that the resulting day will ALWAYS be a Monday through Friday, no matter what you start with. For example, if you start with a Saturday and adjust by zero business days, you'll end up with the following Monday.
This formula assumes two things:
1. There is a field called Start that is the starting date (a date/time field).
2. There is a field called NumDays that has the number of business days to adjust (a number field).
NumFullWeeks := @Integer(NumDays / 5);
ExtraDays := NumDays - (NumFullWeeks * 5);
tempEndingDate := @Adjust(Start; 0; 0; ExtraDays; 0; 0; 0);
W1 := @Weekday(Start);
W2 := @Weekday(tempEndingDate);
AdjustValue := (NumFullWeeks * 7) + ExtraDays + @If(W1 > W2; 2; W1 = 1; 1; W1 = 7; 2; W2 = 1; 1; W2 = 7; 2; 0);
@Adjust(Start; 0; 0; AdjustValue; 0; 0; 0)
First, the number of full weeks to adjust is computed and saved in NumFullWeeks. Next, ExtraDays will hold the number of days in a partial week to adjust (0 to 4).
Then a temporary date is created. The temporary date is only used to compute the weekday of the starting date adjusted by the extra number of days. The code could be shortened by doing the adjust in the W2 line.
W1 and W2 hold the weekdays of the starting and ending dates. Then some computation is done to figure out if either one of those is on a weekend or if they crossed a weekend. That is done in the @If part of AdjustValue.
First, we know that the date is going to be adjusted by the number of full weeks plus that extra value. But it also may need to adjust by 1 or 2 days due to a weekend. If W1 is greater than W2, then the starting date was later in the week than the ending date, so a weekend was crossed and we adjust by an additional 2 days. If the starting date was a Sunday, then we adjust by an extra 1 day to move the starting date to Monday. (Note that if the starting date was Sunday, there's no way the ending date is Saturday because the most number of days the ending date can be adjusted by is 4). If the starting date was on a Saturday, again it needs to move to Monday, so adjust by an extra 2 days. If the ending date was on a Sunday, then move it 1 day to get it to end on a Monday. If the ending date was a Saturday, it needs to move by 2 days to end up on a Monday. All other cases do not need additional adjustment.
After AdjustValue is computed, that is the true number of days to adjust, so the starting date is adjusted by that many days to end up with a date that is a Monday through Friday and that many business days from the starting date.