Solved

Help with tracking Holdays and weekends

Posted on 2003-11-18
13
368 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now