Solved

Help with tracking Holdays and weekends

Posted on 2003-11-18
13
379 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:adspmo
13 Comments
 
LVL 31

Expert Comment

by:qwaletee
ID: 9771781
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9771822
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9771823
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 31

Expert Comment

by:qwaletee
ID: 9771907
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
 
LVL 31

Accepted Solution

by:
qwaletee earned 500 total points
ID: 9772046
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
 
LVL 31

Expert Comment

by:qwaletee
ID: 9772101
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
 
LVL 31

Expert Comment

by:qwaletee
ID: 9772136
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
 

Author Comment

by:adspmo
ID: 9772598
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
 
LVL 31

Expert Comment

by:qwaletee
ID: 9773058
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
 

Author Comment

by:adspmo
ID: 9773451
You lost me on the have DOCUMENTs check

The superstar formula work well so far

James
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9776931
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
 

Author Comment

by:adspmo
ID: 9779185
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
 
LVL 31

Expert Comment

by:qwaletee
ID: 9783876
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to add a username for multiple users in Lotus 6 116
Lotus notes email code 6 116
If no attachment, dont send email - lotus notes/vba 10 387
IBM Domino web server 5 69
For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question