Solved

I need to return a number of days vacation

Posted on 2003-11-19
10
233 Views
Last Modified: 2013-12-18
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
Comment
Question by:adspmo
  • 5
  • 3
  • 2
10 Comments
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 300 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

by:qwaletee
qwaletee earned 100 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

by:HemanthaKumar
ID: 9787727
I just copied what he had !
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:adspmo
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

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

Author Comment

by:adspmo
ID: 9788462
Not according to my @Prompts


I get a count of 4 not 3
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9788522
Let me check.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
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

by:adspmo
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

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a new theme in discussion8.ntf 6 161
Adjusting times for a meeting room 3 145
Incorrect reply-to address Lotus 2 31
Inconsistent date field in Lotus Notes 6 94
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…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

776 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