Holidays In ND6 Formula LanguageWith the looping capabilities of the ND6 formula language, you can now compute holidays that are not on a fixed day of the year. For example, in the US there are holidays like Thanksgiving (4th Thursday in November), Labor Day (1st Monday in September), and Memorial Day (last Monday in May). I'll show you a reusable block of formula language that will compute any of these holidays.
First, you need some fields that define what you're looking for. The reusable code uses 3 number fields: WeekNum holds a number from 1 to 5 which lists the week number. 1 means the first week of the month, 4 means the 4th week of the month, and 5 means the last week of the month. Huh? Look at your calendar for May of 2004. The 4th Monday is May 24th, and the last Monday is May 31st. The 4th Tuesday is May 25th, and the last Tuesday is May 25th (the same day). So, sometimes the 4th "whatever" of the month is the same as the last, and sometimes it isn't. In the US, Memorial Day is the last Monday of May, so it's on the 31st in 2004.
The second field is WeekdayNum. This is the number of the day of the week, just like @Weekday returns. So Sunday is 1, Monday is 2, and so on, until Saturday which is 7.
The third field is MonthNum and is exactly what you would expect. 1 stands for January, 2 for February, and so on, through 12 for December.
This formula computes the next upcoming date according to those 3 fields. In other words, if it's June and you're looking for the first Tuesday in March, it will return the first Tuesday of March next year. But if you're looking for the third Wednesday in July, it will return the third Wednesday in July this year. If you give values for WeekNum, WeekdayNum, or MonthNum that are invalid (not numbers, or outside the bounds) then it returns an empty string (something that isn't a date).
Here's the formula for the computed for display (or computed) field:
CheckWeek := @If(!@IsNumber(WeekNum); @Return(""); WeekNum < 1; @Return(""); WeekNum > 5; @Return(""); 0);
CheckWeekday := @If(!@IsNumber(WeekdayNum); @Return(""); WeekdayNum < 1; @Return(""); WeekdayNum > 7; @Return(""); 0);
CheckMonth := @If(!@IsNumber(MonthNum); @Return(""); MonthNum < 1; @Return(""); MonthNum > 12; @Return(""); 0);
@For(Year := @Year(@Today); Year <= @Year(@Today)+1; Year := Year+1;
Holiday := @Date(Year; MonthNum; 1);
TempDate := @Adjust(@Adjust(Holiday; 0; 1; 0; 0; 0; 0); 0; 0; -1; 0; 0; 0);
DaysInMonth := @Day(TempDate);
@While(@Weekday(Holiday) != WeekdayNum;
Holiday := @Adjust(Holiday; 0; 0; 1; 0; 0; 0)
LowDay := @If(WeekNum = 1; 1; WeekNum = 2; 8; WeekNum = 3; 15; WeekNum = 4; 22; DaysInMonth-6);
HighDay := @If(WeekNum = 1; 7; WeekNum = 2; 14; WeekNum = 3; 21; WeekNum = 4; 28; DaysInMonth);
@While(@Day(Holiday) < LowDay;
Holiday := @Adjust(Holiday; 0; 0; 7; 0; 0; 0)
@While(@Day(Holiday) > HighDay;
Holiday := @Adjust(Holiday; 0; 0; -7; 0; 0; 0)
Holiday := @If(Holiday < @Today; ""; Holiday);
Year := @If(@IsTime(Holiday); Year+9; Year)
Let's take a look at the code in detail. The first three lines make sure our variables are within bounds. If not, exit and set the field value to an empty string. Next, we're going to loop through this year and next year. This will assure that we get a date in the future. I first try this same year and compute the date. At the bottom of the @For loop, the date is compared to @Today. If it's in the past, the value is blanked out and I go on to the next year. But if it's the current date or in the future, I add 9 more to the variable Year (which will get us out of the @For loop immediately) and use the date I computed in the current year.
Inside the loop, I set a date to the first of the month to be processed. Then I adjust by one month forward and then one day backward. This gives us the last of the month to be processed, so I know how many days are in the month. Next, I adjust from the first of the month forward until we're on the right day of the week. So if the first is on a Thursday and we're looking for the "nth" Monday (no matter what "n" is) I'll adjust 4 days at the start to get the variable Holiday to be set on a Monday.
Next, the variables LowDay and HighDay are computed. These are the bounds we're working with, based on the week of the month. For example, if you want the 2nd "whatever" of the month, then the day of the month is always going to be between 8 and 14 (look at your calendar). For the last "whatever" of the month, the range is 6 before the last day of the month (25 if there are 31 days in the month) and the last day of the month.
Then a couple of @While loops adjust a week at a time (remember, earlier we set the date to the right day of the week) until we're in that range. Once I get it in the range, then check to see the date is in the future, as mentioned earlier. Finally, once the outer loop is done, I return the computed date as the field value.
Give it a try. Create a form with 3 editable number fields: WeekNum, WeekdayNum, and MonthNum. Then create a computed date field called whatever you want with the above as its formula. Test the form, put in numbers for the 3 editable fields and press F9 to compute the date.