Former Member

### No. of Business days between two days

Hi,

I want to know is there any custom function somebody had created to find the number of business days between two dates.

Business days = Days excluding Weekends, holidays are not involved.

Any help will be appreciated.

Thanks & Regards

Babin A B

10|10000 characters needed characters exceeded

Former Member
Jan 12, 2015 at 11:53 AM

Hi Babin ,

Please test this function !

---------------------------------------------------

global type date-work-days from function_object
end type

forward prototypes
global function long date-work-days (ref date ad_begdate, ref date ad_enddate)
end prototypes

global function long date-work-days (ref date ad_begdate, ref date ad_enddate);// Function:DATE-WORK-DAYS
// Argument: 1.ad_BegDate = From date
//           2.ad_EndDate = To   date
// Return:Number of work days (Long)
//
// Calling:
// ll_Work_Days=DATE-WORK-DAYS(BegDate,EndDate)

Long ll_Days,ll_Work,ll_Week
Return ll_Work
END IF
ll_Days=ll_Days -1
ll_Days=ll_Days -2
END IF
ll_Days=ll_Days -1
ll_Days=ll_Days -2
END IF
ll_Week=ll_Days/7
ll_Work=ll_Days -ll_Week*2
IF ll_Work<0 THEN
ll_Work=0
END IF
Return ll_Work
end function

----------------

Regards

Jure Gornjak

10|10000 characters needed characters exceeded
• Former Member

If you need to factor in Holidays (and these obviously vary by country), the solution we used was to create a database table.

CREATE table business_calendar (

country_code   smallint  not null,

calendar_date  date       not null,

is_holiday        smallint  not null default 0,

primary key (country_code, calendar_date) );

Then the query to answer your question would be:

SELECT sum(( 1 - is_holiday ) * (CASE when daynumber <= 5 then 1 else 0 end)) as business_days

where country_code = :country

and calendar_date between :start_date and :end_date ;

If a date is a business work day (daynumber 1 thru 5) and NOT a holiday, then the expression evaluates to 1.  Any other combination goes to 0 and is excluded from the sum().

-Paul-

• Jan 12, 2015 at 02:00 PM

Note that Jure's solution assumes that the start date is <= end date.  I would suggest adding logic after the isNull() checks to enforce this additional rule.

10|10000 characters needed characters exceeded
• Former Member

Hi,

Like this

global type date-work-days from function_object
end type

forward prototypes
global function long date-work-days (ref date ad_begdate, ref date ad_enddate)
end prototypes

global function long date-work-days (ref date ad_begdate, ref date ad_enddate);// Function:DATE-WORK-DAYS
// Argument: 1.ad_BegDate = From date
//           2.ad_EndDate = To   date
// Return:Number of work days (Long)
//
// Calling:
// ll_Work_Days=DATE-WORK-DAYS(BegDate,EndDate)

Long ll_Days,ll_Work,ll_Week
ll_Work=0
ELSE
ll_Days=ll_Days -1
ll_Days=ll_Days -2
END IF
ll_Days=ll_Days -1
ll_Days=ll_Days -2
END IF
ll_Week=ll_Days/7
ll_Work=ll_Days -ll_Week*2
IF ll_Work<0 THEN
ll_Work=0
END IF
END IF
Return ll_Work
end function

• Former Member
Jan 12, 2015 at 02:34 PM
10|10000 characters needed characters exceeded
• Former Member
Jan 12, 2015 at 10:25 PM

public function integer bizdaysbetween (date adfrom, date adto);

integer liDate, liTotal, liBiz, liXcept

date ldTest

date ldNotBiz []

ldNotBiz = { 2015-01-01 , 2015-01-26, 2015-04-03, 2015-04-06 } //hard coded

liTotal = daysAfter ( adFrom , adTo )

for liDate = 1 to liTotal

ldTest = relativeDate ( adFrom , liDate )

if dayNumber ( ldTest ) > 1 and dayNumber ( ldTest ) < 7 then

for liXcept = 1 to UpperBound ( ldNotBiz[] )

if ldTest = ldNotBiz [ liXcept ] then

liBiz --

EXIT

end if

next

liBiz++

end if

next

10|10000 characters needed characters exceeded
• Former Member
Jan 14, 2015 at 12:01 AM

Another way of doing it:

date ld1, ld2

integer li_dn1,li_dn2, li_daysback, li_daysforward, li_weeks, li_nowork, li_withwork

// back the beginning date to the nearest Sunday - This is to form the date to the exact week range

li_daysback = li_dn1 - 1

ld1 = RelativeDate(ad_from, -li_daysback)

// Forward the end date to the nearest Saturday - This is to form the date to the exact week range

li_daysforward = 7 - li_dn2

ld2 = RelativeDate(ad_to, li_daysforward)

// Subtract 1 to exclude sunday, if the starting date is not sunday.

If li_daysback <> 0 Then li_daysback = li_daysback - 1

// Subtract 1 to exclude saturday, if the end date is not saturday

If li_daysforward <> 0 Then li_daysforward = li_daysforward - 1

// Determine the number of weeks in the range.

li_weeks = (daysAfter(ld1,ld2) + 1) /7

// 2 non working days per week

li_nowork = li_weeks * 2

// Compute the number of days in the range, less the non working days, less the days you back off to Sunday

// Less the days you forward to Saturday, plus 1 because daysAfter starts counting after the start date.

li_withwork = daysAfter(ld1,ld2) - li_daysback - li_daysforward - li_nowork + 1

Return li_withwork