Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with tracking Holdays and weekends

Posted on 2003-11-18
13
Medium Priority
?
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

718 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