I need to return a number of days vacation

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


adspmoAsked:
Who is Participating?
 
HemanthaKumarConnect With a Mentor Commented:
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
 
qwaleteeConnect With a Mentor Commented:
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
 
HemanthaKumarCommented:
I just copied what he had !
0
Hire Technology Freelancers with Gigs

Work with 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.

 
adspmoAuthor Commented:
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
 
HemanthaKumarCommented:
WorkDayCount variable in above formula gives that info.
0
 
adspmoAuthor Commented:
Not according to my @Prompts


I get a count of 4 not 3
0
 
HemanthaKumarCommented:
Let me check.
0
 
HemanthaKumarCommented:
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
 
adspmoAuthor Commented:
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
 
qwaleteeCommented:
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
All Courses

From novice to tech pro — start learning today.