Solved

I need to return a number of days vacation

Posted on 2003-11-19
10
236 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

685 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