No. of Business days between two days

[Former Member]

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

[Former Member · Accepted solution]

Hi Babin ,

Please test this function !

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

$PBExportHeader$date-work-days.srf
$PBExportComments$Ime dneva
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
IF IsNull(ad_BegDate) OR IsNull(ad_EndDate) THEN
Return ll_Work
END IF
ll_Days=DaysAfter(ad_BegDate,ad_EndDate)+1
IF DayNumber(ad_BegDate)=1 THEN
ll_Days=ll_Days -1
ELSEIF  DayNumber(ad_BegDate)=7 THEN
   ll_Days=ll_Days -2
END IF
IF DayNumber(ad_EndDate)=7 THEN
ll_Days=ll_Days -1
ELSEIF DayNumber(ad_EndDate)=1 THEN
   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

[Former Member · Accepted solution]

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

from business_calendar

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-

[Former Member]

Another way of doing it:

public function integer of_businessdays (date ad_from, date ad_to);

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_dn1 =  dayNumber(ad_from)

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_dn2 = dayNumber(ad_to)

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

[Former Member]

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

[Former Member]

Here's an SQL solution...

http://stackoverflow.com/questions/6425255/return-number-of-weekdays-between-2-dates-in-t-sql

hth,

Mark

[Former Member]

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.

[Former Member]

Hi,

Like this

$PBExportHeader$date-work-days.srf
$PBExportComments$Ime dneva
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
IF IsNull(ad_BegDate) OR IsNull(ad_EndDate) OR ad_BegDate>ad_EndDate THEN
    ll_Work=0
ELSE
   ll_Days=DaysAfter(ad_BegDate,ad_EndDate)+1
   IF DayNumber(ad_BegDate)=1 THEN
      ll_Days=ll_Days -1
   ELSEIF  DayNumber(ad_BegDate)=7 THEN
      ll_Days=ll_Days -2
   END IF
   IF DayNumber(ad_EndDate)=7 THEN
      ll_Days=ll_Days -1
   ELSEIF DayNumber(ad_EndDate)=1 THEN
      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