Skip to Content
avatar image
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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 12, 2015 at 11:53 AM

    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

    Add comment
    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

      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-

  • 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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Jan 12, 2015 at 02:34 PM
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 14, 2015 at 12:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded