Help with tracking Holdays and weekends

Hi There

I have scanned the database looking for a solution , however they all seem to do more than I need(I think).

I need to calculatre out weekends and holidays. I have the following that does weekends

diffDays := (EndDate - StartDate) / 86400 + 1;
strtDay := @Modulo(@Weekday(StartDate); 7);
endDay := @Modulo(@Weekday(EndDate); 7);
result := (diffDays - endDay + strtDay - 8) * 5 / 7 - @Max(-2; -strtDay) - @Min(1; endDay) + 5 - strtDay + endDay ;
 result


The other caveate is that this is for a multinational company therefore depending on the value in the Country field there may or may not be a holiday.

e.g. United States 27/11/2003
We in Canada and United Kingdom do not get that as a Holiday

I know there will have to form with the holidays on it and a view. The current form contains an Event/Holiday field and a Start and End Date. Is there any thing else this form requires? Country Field maybe or can I look up to the employee profile document to retrieve this?


Any help greatly appreciated




Regards

James
adspmoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

qwaleteeCommented:
The Domino directory nornallymaintains a list of holidays per country, though yours may be out of date.  Check it out.

It is difficult to determine which country a user is in if you do not use geography as part of your certificate structure, and dont place address info in your Person documents.  (Either solution woud work.)

Now, as to a formula for eliminating holidays...
0
HemanthaKumarCommented:
0
ksi2001Commented:
There is a view and corresponding form in the Notes directory for the holidays (Server\Holidays). You will need a country field on such forms to distinguish between different countries holidays.

As for the actual document you can either have country as an editable field or you can retrieve it from the person document in NotesDirectory or from some custom employee database.

If you are going to use Notes Directory, it should be maintained properly (holidays kept updated, each person should have country field filled, etc.) It is an organizational issue more than technical.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

qwaleteeCommented:
Looks like you used some of my old code above.  I like a different solution for when holidays are involved. It is conceptually easier to understand, though less efficient.

StartDate := [1/1/2003]; "EXAMPLE";
EndDate := [4/4/2003] "EXAMPLE";
dateRangeText := @Text(StartDate) + " - " + @Text(EndDate);
dateRange := @TextToTime(dateRangeText);
dateRangeTextList := @Explode(dateRange);
dateRangeDateList := @TextToTime(dateRangeTextList);
dayOfWeekNumberList := @Weekday(dateRangeDateList);
dayOfWeekTextList := @Text(dayOfWeekNumberList);
weekEndList := @Replace(dayOfWeekTextList; "2" : "3" : "4" : "5" : "6"; "IS A WEEKDAY");
dateRangeWithComment := dateRangeDateList + weekEndList;
weekDayDates := @Left(dateRangeWithComment; "IS A WEEKDAY");
holidayDateList := [2/2/2003] : [3/3/2003]; "Just an example, replace with a lookup thatreturns date values as a list";
holidayTextList := @Text(holidays);
workDayDateTextList := @Replace(weekDayDates; holidayTextList; "");
finalTextList := @Trim(workDayDateTextList);
finalDateList := @TextToTime(finalTextList)

Now, I have to go test this.  I wrote it once before, but could not find it, so I just rewote it in a text editor.
0
qwaleteeCommented:
Had two correct two or three mistakes:

StartDate := [01/01/2003]; "EXAMPLE";
EndDate := [04/04/2003]; "EXAMPLE";
dateRangeText := @Text(StartDate) + " - " + @Text(EndDate);
dateRange := @TextToTime(dateRangeText);
dateRangeTextList := @Explode(dateRange);
dateRangeDateList := @TextToTime(dateRangeTextList);
dayOfWeekNumberList := @Weekday(dateRangeDateList);
dayOfWeekTextList := @Text(dayOfWeekNumberList);
weekEndList := @Replace(dayOfWeekTextList; "2" : "3" : "4" : "5" : "6"; "" : "" : "" : "" : "" : "" + "IS A WEEKDAY");
dateRangeWithComment := dateRangeTextList + weekEndList;
weekDayDates := @Left(dateRangeWithComment; "IS A WEEKDAY");
holidayDateList := [02/02/2003] : [03/03/2003]; "Just an example, replace with a lookup thatreturns date values as a list";
holidayTextList := @Text(holidayDateList);
workDayDateTextList := @Replace(weekDayDates; holidayTextList; "");
finalTextList := @Trim(workDayDateTextList);
finalDateList := @TextToTime(finalTextList);
workDayCount := @Elements(finalDateList);
@Prompt([OKCANCELLIST] : [NoSort]; @Text(workDayCount ); ""; ""; finalTextList)

The test case:
From January 1st to April 4th is 94 days inclusive
There are two holidays
If you check, you will find 13 full weekends -- 26 days
So, we shoudl subtract 28 days, and get 66 days.

But... Feb 2, one of the holidays, is also a Sunday, and woud be double-counted.
So, we subtract only 27 days, and get 67 workdays.

Which is exactly what the prompt reports.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qwaleteeCommented:
Here is a very compact but densely coded version -- they'll think you are a formula coding superstar, but obnoxious:

StartDate := [01/01/2003]; "EXAMPLE";
EndDate := [04/04/2003]; "EXAMPLE";
holidayDateList := [02/02/2003] : [03/03/2003]; "Just an example, replace with a lookup thatreturns date values as a list";
dateRangeTextList := @Explode(@TextToTime(@Text(StartDate) + " - " + @Text(EndDate)));
@Elements(@Trim(@Replace(@Left(dateRangeTextList + @Replace(@Text(@Weekday(@TextToTime(dateRangeTextList))); "2" : "3" : "4" : "5" : "6"; "" : "" : "" : "" : "" : "" + "IS A WEEKDAY"); "IS A WEEKDAY"); @Text(holidayDateList); "")));
0
qwaleteeCommented:
Note that this will work if there are no holidays looked up, if theholidays looked up are "oyut of range" (i.e., it is OK to return last year's holidays), r even if the lookup returns an error.  Obviously, in all those cases, holidays will not be factored out.
0
adspmoAuthor Commented:
This keeps getting better

I have Calendar View
In the calendar view I only want to show the Weekdays ,if a holiday is booked during the week it is also skipped


So when someone looks at the calendar the only see the actual days off excluding e=weekends and holidays

Will the above work in a view
James
0
qwaleteeCommented:
You can't do lookups in a view, so you can't have the VIEW CODE decide on holidays (unless you hard=code it all).

Instead, if youhave control over the document posting process, have the DOCUMENTS check whether their dates fall out on a holiday, and skip days are weekend/holiday.
0
adspmoAuthor Commented:
You lost me on the have DOCUMENTs check

The superstar formula work well so far

James
0
qwaleteeCommented:
Have the form calcuate the values to display in the view.  The form can do the lookup, and create extra fields on the form to hold the "redacted" date list.  The view can then "play dumb," and merely used teh value calculated on the form into the extra fields.

This of course requires taht all document creation and editing be done through the form.  If someone pastes a doc in or uses a script to create a document, it wil not have the weekend/holiday redaction calculations on it.
0
adspmoAuthor Commented:
I have a field called NewDaysTotal. The formula within that field is the superstar formula above.However that only contains the number of actual days. Do I have to modify and use Hemanths suggestion for view calculations in  the form in a different field. I figure  I can then do lookup for the holidays.

James




Start := [12-1-2003];
End := [1-1-2004];
Holidays := [12-25-2003];

rem "days we dont want";
remove:="1":"7";

rem "list of all dates in range";
dates:=@Explode(@TextToTime(@Implode(@Text(start:end);"-")));
rem "list of all weekdays in range";
days:=@text(@Weekday(@texttotime(dates)));
rem "remove those we dont want";
weekdays:=@Replace(days;remove;"");
rem "extract weekday dates";
t := @texttotime(@trim(@Replace(dates;dates+weekdays;"")));
@TextToTime(@Trim(@Replace( @Text(t); @Text(Holidays); "")));



0
qwaleteeCommented:
James,

I'm not sure what you are suggesting.  Tell me if I understand this correctly.  You are saying that the formula gave you is god for the NewDaysTotal field.  But you want to do other calculations on the data as well, and you think you want to do those calculations in the view instead of the form.  You would like to apply the holiday calculation after those "other" calculations.

Problem #1: If you do you calculations in a view, you absolutely CAN'T due holiday calculations.  Views can't request @DbLookups or @DbColumns.  Period.  So that part certainly has to be done in the form.

Problem #2: You don't describe what those "other calculations" are, and you don't descrbe the rational for wanting to do those calculations in the view instead of, as I suggested, in the form.

Problem #3: Why did you open an additional question on this topic?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.