@BusinessDays issue

HCL Domino Domino Forum
1

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 holidays 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

2

Subject: @BusinessDays issue

I would change a line to your code to:

@Prompt([Ok];“Business days”;@Text(@BusinessDays(dtFromDate; dtToDate; 1 : 7; @TextToTime(txtholidays))))

Let me know if this helps!

Gabriel Amorim.

3

Subject: RE: @BusinessDays issue

Hi GabrielThanks for quick reply.

I have also tried this but it is not excluding the holidays.

The results is same.

Sanjay

4

Subject: RE: @BusinessDays issue

I managed to get your code working also like this:

The form I used contains the HolidayDatesAll field which is of a Date/Time type and ‘Allow multiple values’. I put it in a view, third column, just for me to check the lookup in a different manner.

currDate:= @Today;

currYear := @Text(@Year(currDate));

holidays := @DbLookup(“”:“NoCache”; @DbName; “vwHolidays”; currYear; 3);

@StatusBar("DbLookup "+@Text(holidays));

@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; holidays)))

It worked pretty well.

Check that.

5

Subject: RE: @BusinessDays issue

Hi,I have been following ur post and tried the same myself but I did not get holidays populated anyways.

It still gives the number of days excluding saturdays,sundays but including holidays.

Any followup will be deeply appreciated

6

Subject: @BusinessDays issue

txtHolidays is a string list, not a list of dates. Why not just use holidays (which is a date list)?

7

Subject: RE: @BusinessDays issue

Hi StanIf I use holidays, getting error message “Incorrect data type for operator or @function date/time expected”.

Sanjay

8

Subject: RE: @BusinessDays issue

Well, txtHolidays has the same problem. Use @TextToTime(@Trim(@Text(holidays))) to make sure. The @For loop is an abomination that should never have been allowed to happen in that formula.

9

Subject: RE: @BusinessDays issue

Hi StanYou are gr8, It’s working.

Thanks to gabriel also.

Sanjay