Hello, I need some assistance with the @businessdays function. The challenge that I am having is with eliminating the holidays. The goal is the use the exisiting repeating holidays in the domino directory. We have holidays listed to various countries but I only need the US holidays. Any ideas on how to accomplish this?
Subject: @businessdays eliminating holidays
We found something that works for us so hopefully this will help someone else.
Given a list of holidays, we created a view called HolidayView listing those dates. This formula was used to convert the date to a text value.
@Text(HolidayDate;“S0”)
We then used a field on a form that would calculate the net business days while eliminating the weekends (Sat = 1 and Sun = 7) and holidays.
HolidayList := @TextToTime(@DbColumn("":"";"":"";"HolidayView";1));
weekends := 1:7;
@BusinessDays(StartDate; EndDate; weekends;HolidayList));
“”)
Subject: @businessdays eliminating holidays
You ask a rather complex question. The reason is that holidays don’t always repeat the same way. Labour Day is always the first Monday in September, independent of what day of the month that actually falls on, while Christmas is always the 25th of December, regardless of what day of the week that falls on. Additionally, some holidays can be moved to either the Friday or Monday if they fall on the weekend. And the Domino Directory has created the Holiday documents to account for each of these situations.
To sort out which documents need to have their RepeatCustom field used as opposed to some other field, is a real challenge. You may be better off to use this tip:
perhaps converting it to @Formula if you want to keep everything in that language.
Subject: RE: @businessdays eliminating holidays
As a little exercise, I did the conversion to Formula myself.
REM “====================================”;
@Set(“hYear”; @Year(@Today));
@Set(“hYear”; @Prompt([OkCancelEdit]; “Year selection”; “Enter the year for the list of holidays”; @Text(hYear)));
@Set(“hYear”; @If(
@IsError(@TextToNumber(hYear));
@Return("");
@Integer(@TextToNumber(hYear))
));
@If(
hYear > 3000 | hYear < 0;
@Return("");
""
);
@Set(“hNames”;
“New Year’s Day”:
“Martin Luther King Jr Day”:
“Groundhog Day”:
“Valentine’s Day”:
“President’s Day”:
“St. Patrick’s Day”:
“Easter Sunday”:
“Good Friday”:
“Mother’s Day”:
“Memorial Day”:
“Father’s Day”:
“Independence Day”:
“Labor Day”:
“Columbus Day”:
“Halloween”:
“Veteran’s Day”:
“Thanksgiving”:
“Christmas Day”
);
REM {New Year’s Day};
@Set(“h1”; @Date(hYear; 1; 1));
REM {Martin Luther King Jr Day};
@Set(“h2”; @Date(hYear; 1; 15));
@Set(“wkDay”; @Weekday(h2));
@Set(“h2”; @Adjust(h2; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {Groundhog Day};
@Set(“h3”; @Date(hYear; 2; 2));
REM {Valentine’s Day};
@Set(“h4”; @Date(hYear; 2; 14));
REM {President’s Day};
@Set(“h5”; @Date(hYear; 2; 15));
@Set(“wkDay”; @Weekday(h5));
@Set(“h5”; @Adjust(h5; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {St. Patrick’s Day};
@Set(“h6”; @Date(hYear; 3; 17));
REM {Easter Sunday};
c := @Integer( hYear / 100 );
n := hYear - 19 * @Integer( hYear / 19 );
k := @Integer(( c - 17 ) / 25 );
i := c - @Integer(c / 4) - @Integer(( c - k ) / 3) + 19 * n + 15;
i := i - 30 * @Integer( i / 30 );
i := i - @Integer( i / 28 ) * ( 1 - @Integer( i / 28 ) * @Integer( 29 / ( i + 1 ) ) * @Integer( ( 21 - n ) / 11 ) );
j := hYear + @Integer( hYear / 4 ) + i + 2 - c + @Integer( c / 4 );
j := j - 7 * @Integer( j / 7 );
l := i - j;
m := 3 + @Integer( ( l + 40 ) / 44);
d := l + 28 - 31 * @Integer( m / 4 );
@Set(“h7”; @Date(hYear; m; d));
REM {Good Friday};
@Set(“h8”; @Adjust(h7; 0; 0; -2; 0; 0; 0));
REM {Mother’s Day};
@Set(“h9”; @Date(hYear; 5; 8));
@Set(“wkDay”; @Weekday(h9));
@Set(“h9”; @Adjust(h9; 0; 0; 1 - wkDay + @If(wkDay > 1; 7; 0); 0; 0; 0));
REM {Memorial Day};
@Set(“h10”; @Date(hYear; 5; 25));
@Set(“wkDay”; @Weekday(h10));
@Set(“h10”; @Adjust(h10; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {Father’s Day};
@Set(“h11”; @Date(hYear; 6; 15));
@Set(“wkDay”; @Weekday(h11));
@Set(“h11”; @Adjust(h11; 0; 0; 1 - wkDay + @If(wkDay > 1; 7; 0); 0; 0; 0));
REM {Independence Day};
@Set(“h12”; @Date(hYear; 7; 4));
REM {Labor Day};
@Set(“h13”; @Date(hYear; 9; 1));
@Set(“wkDay”; @Weekday(h13));
@Set(“h13”; @Adjust(h13; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {Columbus Day};
@Set(“h14”; @Date(hYear; 10; 8));
@Set(“wkDay”; @Weekday(h14));
@Set(“h14”; @Adjust(h14; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {Halloween};
@Set(“h15”; @Date(hYear; 10; 31));
REM {Veteran’s Day};
@Set(“h16”; @Date(hYear; 11; 11));
REM {Thanksgiving};
@Set(“h17”; @Date(hYear; 11; 22));
@Set(“wkDay”; @Weekday(h17));
@Set(“h17”; @Adjust(h17; 0; 0; 5 - wkDay + @If(wkDay > 5; 7; 0); 0; 0; 0));
REM {Christmas Day};
@Set(“h18”; @Date(hYear; 12; 25));
@Set(“hDates”;
h1:
h2:
h3:
h4:
h5:
h6:
h7:
h8:
h9:
h10:
h11:
h12:
h13:
h14:
h15:
h16:
h17:
h18
);
@Prompt([OkCancelList]:[NoSort]; “Holidays for year " + @Text(hYear); “The following list does not attempt to move holidays that fall on weekends”; “”; hNames + " —> [ " + @Text(hDates) + " ]”);
REM “====================================”;
Now, if you want to do the additional work of moving holidays that should be moved if they fall on a weekend, the bulk of the work has already been done and it should be a simple matter to move them.
A lot of the code above is more for presenting a dialog box to the user - you really only need the list that I created that is stored in hDates as the parameter that you pass to the @BusinessDays function of dates to exclude.
Also, if there are any dates that you do not wish to commemorate, just remove their calculation from the list.
Enjoy…
Subject: RE: @businessdays eliminating holidays
Further modifying my previous code, this code gives you either a list of weekday dates for a chosen year that do not include the holidays you specify or use the @BusinessDays function to calculate the number of days in the year by removing those same holidays.
REM {====================================};
@Set(“holidayYear”; @Year(@Today));
@Set(“holidayYear”; @Prompt([OkCancelEdit]; “Year selection”; “Enter the year for the list of holidays”; @Text(holidayYear)));
@Set(“holidayYear”; @If(
@IsError(@TextToNumber(holidayYear));
@Return("");
@Integer(@TextToNumber(holidayYear))
));
@If(
holidayYear > 3000 | holidayYear < 0;
@Return("");
""
);
@Set(“holidayNames”;
“New Year’s Day”:
“Martin Luther King Jr Day”:
“Groundhog Day”:
“Valentine’s Day”:
“President’s Day”:
“St. Patrick’s Day”:
“Easter Sunday”:
“Good Friday”:
“Mother’s Day”:
“Memorial Day”:
“Father’s Day”:
“Independence Day”:
“Labor Day”:
“Columbus Day”:
“Halloween”:
“Veteran’s Day”:
“Thanksgiving”:
“Christmas Day”
);
REM {New Year’s Day};
@Set(“h1”; @Date(holidayYear; 1; 1));
REM {Martin Luther King Jr Day};
@Set(“h2”; @Date(holidayYear; 1; 15));
@Set(“wkDay”; @Weekday(h2));
@Set(“h2”; @Adjust(h2; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {Groundhog Day};
@Set(“h3”; @Date(holidayYear; 2; 2));
REM {Valentine’s Day};
@Set(“h4”; @Date(holidayYear; 2; 14));
REM {President’s Day};
@Set(“h5”; @Date(holidayYear; 2; 15));
@Set(“wkDay”; @Weekday(h5));
@Set(“h5”; @Adjust(h5; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {St. Patrick’s Day};
@Set(“h6”; @Date(holidayYear; 3; 17));
REM {Easter Sunday};
c := @Integer( holidayYear / 100 );
n := holidayYear - 19 * @Integer( holidayYear / 19 );
k := @Integer(( c - 17 ) / 25 );
i := c - @Integer(c / 4) - @Integer(( c - k ) / 3) + 19 * n + 15;
i := i - 30 * @Integer( i / 30 );
i := i - @Integer( i / 28 ) * ( 1 - @Integer( i / 28 ) * @Integer( 29 / ( i + 1 ) ) * @Integer( ( 21 - n ) / 11 ) );
j := holidayYear + @Integer( holidayYear / 4 ) + i + 2 - c + @Integer( c / 4 );
j := j - 7 * @Integer( j / 7 );
l := i - j;
m := 3 + @Integer( ( l + 40 ) / 44);
d := l + 28 - 31 * @Integer( m / 4 );
@Set(“h7”; @Date(holidayYear; m; d));
REM {Good Friday};
@Set(“h8”; @Adjust(h7; 0; 0; -2; 0; 0; 0));
REM {Mother’s Day};
@Set(“h9”; @Date(holidayYear; 5; 8));
@Set(“wkDay”; @Weekday(h9));
@Set(“h9”; @Adjust(h9; 0; 0; 1 - wkDay + @If(wkDay > 1; 7; 0); 0; 0; 0));
REM {Memorial Day};
@Set(“h10”; @Date(holidayYear; 5; 25));
@Set(“wkDay”; @Weekday(h10));
@Set(“h10”; @Adjust(h10; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {Father’s Day};
@Set(“h11”; @Date(holidayYear; 6; 15));
@Set(“wkDay”; @Weekday(h11));
@Set(“h11”; @Adjust(h11; 0; 0; 1 - wkDay + @If(wkDay > 1; 7; 0); 0; 0; 0));
REM {Independence Day};
@Set(“h12”; @Date(holidayYear; 7; 4));
REM {Labor Day};
@Set(“h13”; @Date(holidayYear; 9; 1));
@Set(“wkDay”; @Weekday(h13));
@Set(“h13”; @Adjust(h13; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {Columbus Day};
@Set(“h14”; @Date(holidayYear; 10; 8));
@Set(“wkDay”; @Weekday(h14));
@Set(“h14”; @Adjust(h14; 0; 0; 2 - wkDay + @If(wkDay > 2; 7; 0); 0; 0; 0));
REM {Halloween};
@Set(“h15”; @Date(holidayYear; 10; 31));
REM {Veteran’s Day};
@Set(“h16”; @Date(holidayYear; 11; 11));
REM {Thanksgiving};
@Set(“h17”; @Date(holidayYear; 11; 22));
@Set(“wkDay”; @Weekday(h17));
@Set(“h17”; @Adjust(h17; 0; 0; 5 - wkDay + @If(wkDay > 5; 7; 0); 0; 0; 0));
REM {Christmas Day};
@Set(“h18”; @Date(holidayYear; 12; 25));
@Set(“holidayDates”;
h1:
h2:
h3:
h4:
h5:
h6:
h7:
h8:
h9:
h10:
h11:
h12:
h13:
h14:
h15:
h16:
h17:
h18
);
@Set(“holidayDaysOfWeek”; @Weekday(holidayDates));
@Set(“holidayBusWeekDays”; @Replace(@Text(holidayDaysOfWeek); “1”:“2”:“3”:“4”:“5”:“6”:“7”; “1”:“0”:“0”:“0”:“0”:“0”:“-1”)); REM {Move Saturdays to Fridays, and Sundays to Mondays};
@Set(“holidayBusWeekHolidays”; “”);
@For(
n := 1;
n <= @Elements(holidayBusWeekDays);
n := n + 1;
holidayBusWeekHolidays := holidayBusWeekHolidays : @Text(@Adjust(holidayDates[n]; 0; 0; @TextToNumber(holidayBusWeekDays[n]); 0; 0; 0))
);
@Set(“holidayBusWeekHolidays”; @TextToTime(@Trim(holidayBusWeekHolidays)));
@Prompt([OkCancelList]:[NoSort]; “Adjusted holidays for year " + @Text(holidayYear); “The following list indicates all holidays for the year " + @Text(hYear) + " and moves those that fall on weekends to a weekday”; “”; holidayNames + " —> [ " + @Text(holidayBusWeekHolidays) + " ]”);
@Set(“datesFullYear”; @TextToTime(@Explode(@TextToTime(“[” + @Text(@Date(holidayYear; 1; 1)) + " - " + @Text(@Date(holidayYear; 12; 31)) + “]”))));
@Set(“datesDaysOfWeek”; @Weekday(datesFullYear));
@Set(“datesWeekdaysOnly”; @Text(datesFullYear) + “|” + @Replace(@Text(datesDaysOfWeek); “1”:“2”:“3”:“4”:“5”:“6”:“7”; “x”:“”:“”:“”:“”:“”:“x”));
@Set(“datesWeekdaysOnly”; @Left(@Trim(@Replace(datesWeekdaysOnly; @Trim(@Left(datesWeekdaysOnly; “|x”)) + “|x”; “”)); “|”));
@Set(“datesWeekdaysOnly”; @TextToTime(datesWeekdaysOnly));
@Set(“datesBusinessDaysNoHolidays”; @TextToTime(@Trim(@Replace(@Text(datesWeekdaysOnly); @Text(holidayBusWeekHolidays); “”))));
@Prompt([OkCancelList]:[NoSort]; "Workdays for the year " + @Text(holidayYear); “This is the list of business days in year " + @Text(holidayYear) + " with holidays removed”; “”; @Text(datesBusinessDaysNoHolidays));
@Prompt([Ok]; “Compare counts”; "@BusinessDays = " + @Text(@BusinessDays(@Date(holidayYear; 1; 1); @Date(holidayYear; 12; 31); 1:7; holidayBusWeekHolidays)) + @NewLine + "@Elements = " + @Text(@Elements(datesBusinessDaysNoHolidays)));
REM {====================================};
Subject: RE: @businessdays eliminating holidays
Thanks for your help. I’ve been trying to perform a dblookup and pull those holidays from the domino directory. Unfortunately it only pulls the first date and does not recognize that these are repeating holidays. We recognize Federal holidays which I was able to locate for the next 3 years. The fed holidays recognizes the ‘observed’ date and takes into account if the holiday falls on the weekend. My code is similar to yours in that the holidays are hard-coded. This should buy me some time for now to figure out a long term solution.
Subject: RE: @businessdays eliminating holidays
That is precisely the reason why doing a lookup is difficult. In some cases, you need to look up one field and in others you need to look up other fields (like RepeatCustom). Then you need to filter out the blanks from each (because not all entries will have a RepeatCustom) and additionally, you need to apply the repeat rule if there is no RepeatCustom.
However, in my solution, there isn’t really any hard coding - the dates are calculated correctly for any particular year and any holidays your company does not commemorate can simply be removed from the list.
There is some hard-coding in terms of what to do with holidays that fall on the weekend and I just apply a generic rule that moves any holiday falling on the weekend to the nearest business day. I recognize that this is not applicable to all the holidays (such as Mother’s Day which is not moved from the Sunday) but that can be applied at each individual date calculation rather than at the end. Once that is done, this should serve as a general purpose holiday calculation for any given year.