Solved

I need to return a number of days vacation

Posted on 2003-11-19
10
231 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

19 Experts available now in Live!

Get 1:1 Help Now