Hi All
My requirement is to calculate the no. of working days between two given dates except Saturdays, Sundays and Holidays.
My holiday list is in the view “Holidays” and the field name is “HolidayDateAll” datatype is Text List . The values in the field are as below
“01/01/2009”
“05/26/2009”
“07/04/2009”
“09/01/2009”
“11/27/2009”
“12/25/2009”
I have written the two types of formulas :
1>
@If(txtLeavetype = “”;@Do(@Prompt([Ok]; “Alert” ; “Kindly select leave type”);@Return(“”));“”);
@If(dtFromDate = “”;@Do(@Prompt([Ok]; “Alert” ; “Kindly select from date”);@Return(“”));“”);
@If(dtToDate = “”;@Do(@Prompt([Ok]; “Alert” ; “Kindly select to date”);@Return(“”));“”);
@If(dtFromDate > dtToDate;@Do(@Prompt([Ok];“Error Alert”;“To Date cannot be less than From Date”);@Return(“”));“”);
holidays := [01/01/2009] : [05/26/2009] : [07/04/2009] : [09/01/2009] : [11/27/2009] : [12/25/2001];
@Prompt([Ok];“No of working days”;"Business Days between " + @Text(dtFromDate) + " - " + @Text(dtToDate) + @NewLine + @Text(@BusinessDays(dtFromDate; dtToDate; 1 : 7; holidays)))
It is working fine but the problem is I have to change the holiday list in code every year, which is not recommendable.
2>
currDate:= @Today;
currYear := @Text( @Year(currDate));
holidays := @DbLookup(“”; @DbName; “Holidays”; currYear; “HolidayDateAll”);
@For( n := 1; n <= @Elements(holidays); n := n + 1; txtholidays := txtholidays + “[”+holidays[n]+“]” + “:” );
@If( dtFromDate = “”; @Do(@Prompt([Ok]; “Alert” ; “Kindly select from date”); @Return(“”)); “”);
@If( dtToDate = “”; @Do(@Prompt([Ok]; “Alert” ; “Kindly select to date”); @Return(“”)); “”);
@If( dtFromDate > dtToDate; @Do(@Prompt([Ok];“Error Alert”;“To Date cannot be less than From Date”); @Return(“”)); “”);
@Prompt([Ok];“Business days”;@Text(@BusinessDays(dtFromDate; dtToDate; 1 : 7; txtholidays)))
This code is not excluding the holidays.
Thanks,
Sanjay