[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# I need to return a number of days vacation

Posted on 2003-11-19
Medium Priority
241 Views
I need to calculate 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.

This returns a NUMBER, however that number still includes holidays

I would like to subtract the holidays as well

So if I book holidays  12/22/2003 - 12/26/2003 I am only charge for three days not five

I have the holidays listed in a view sorted by Country

SO

If I am in Canada and  there is a holiday within the range of dates I booked off then I do not want to be charged for that day

I think the lookup will have to be based on Country and Date Range

What do you think

0
• 5
• 3
• 2

LVL 24

Accepted Solution

HemanthaKumar earned 1200 total points
ID: 9782909
The code that I gave you earlies does that ... But what is that you are finding it difficult ?

Let me clarify some things over here

This leave document that is being approved should be left alone. Create another document say Calendar and include following fields

Country - Set from Leave form
UserName - User should be set from the approval of the leave form
Holidays - Computed field which does a lookup to the holiday view, with formula (DateTime Type)
@TextToTime(@DbLookup( "" : "NoCache" ;""; "Holidays" ; Country;3));

StartDate - Set from leave form
EndDate - Set from leave form

EffectiveDates - Calculated with following formula (DateTime type)

StartDate := @Text(StartDate);
EndDate := @Text(EndDate);
holidayDateList := Holidays;

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");

holidayTextList := @Text(holidayDateList);
workDayDateTextList := @Replace(weekDayDates; holidayTextList; "");
finalTextList := @Trim(workDayDateTextList);
finalDateList := @TextToTime(finalTextList);
workDayCount := @Elements(finalDateList);
@TextToTime(finalTextList)

Once you approve and save the form create this calendar entry form for the user and Issue ComputeWithForm method so that all the computed fields are calculated accordingly.

Hope this clears things little bit

~Hemanth
0

LVL 31

Assisted Solution

qwaletee earned 400 total points
ID: 9784015
Hi HemanthaKumar,
> holidayTextList := @Text(holidayDateList);
> workDayDateTextList := @Replace(weekDayDates; holidayTextList; "");
> finalTextList := @Trim(workDayDateTextList);
> finalDateList := @TextToTime(finalTextList);
> workDayCount := @Elements(finalDateList);
> @TextToTime(finalTextList)

And why not just:

holidayTextList := @Text(holidayDateList);
workDayDateTextList := @Replace(weekDayDates; holidayTextList; "");
finalTextList := @Trim(workDayDateTextList);
@TextToTime(finalTextList);

Best regards,
qwaletee
0

LVL 24

Expert Comment

ID: 9787727
I just copied what he had !
0

Author Comment

ID: 9788348
I will try to clarify

I need the calculation to return an integer

Calculate the range of holidays booked remove the weekends and holidays

If i book December 22 2003 to December 26 2003 that is five days .
Minus Two Days for holidays = 3

That is what I need
0

LVL 24

Expert Comment

ID: 9788408
WorkDayCount variable in above formula gives that info.
0

Author Comment

ID: 9788462
Not according to my @Prompts

I get a count of 4 not 3
0

LVL 24

Expert Comment

ID: 9788522
Let me check.
0

LVL 24

Expert Comment

ID: 9788624
If I have 25th as hoiliday I get 4,

If I have 25 and 26 as holiday I get 3.

Did you make sure the country column formula is trimmed...?
0

Author Comment

ID: 9788681
I now have it working

I apoligize for all the queation and problems

Thanx again

StartDate := @Text(StartDate);
EndDate := @Text(EndDate);
Country := cur_country;
holidayDateList := @Text(@DbLookup( "" : "NoCache" ;""; "Holidays" ; Country;3));

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");

holidayTextList := @Text(holidayDateList);
workDayDateTextList := @Replace(weekDayDates; holidayTextList; "");
finalTextList := @Trim(workDayDateTextList);
finalDateList := @TextToTime(finalTextList);
workDayCount := @Elements(finalDateList);
workDayCount

0

LVL 31

Expert Comment

ID: 9805080
HemanthaKumar,
> I just copied what he had !
Yeah, but you didn't notice that the calculation you were performing was redundant to what was already in the code.  He just copied what I gave him, you just piled a bit on to it, but there was no need.
0

## Featured Post

Question has a verified solution.

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

Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the adminā¦
Loops Section Overview
###### Suggested Courses
Course of the Month19 days, 6 hours left to enroll