Skip to Content
avatar image
Former Member

Number of days between two dates excluding weekends

Hi Experts,

I need to calculate number of days(which excludes weekends) between two dates.

Example:

date1: 10/15/2015;

date2: 11/03/2015;

Result = date1 - date2;

Result = 19 days; (with weekends)

Result should be 13 days excluding weekends.

Can you please help me, how to implement excluding weekends.

Thanks & Regards,

Balamurugan G

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 03, 2015 at 09:47 AM

    Create a calendar (table) in your database. Use the Date_Generation transform to that extent. Include a WORKING_DAY_NO column to the calendar that contains an ever increasing sequence number counting the number of working days since the beginning of your calendar. WORKING_DAY_NO can be easily populated with a call to a function f_working_days(DATE) in its mapping:

         if (isweekend($P_DATE)=0) 
           begin
           $G_working_day = $G_working_day + 1;
           end

         return ($G_working_day);

    $G_working_day is a global variable, initialised to 0.

    Calculating the number of business days between date1 and date2 is a piece of cake, now:

    lookup(calendar, WORKING_DAY_NO, 0, 'PRE_LOAD_CACHE', DATE , date2) - lookup(calendar, WORKING_DAY_NO, 0, 'PRE_LOAD_CACHE', DATE , date1)

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 03, 2015 at 10:22 PM

    Here's a piece of code that you can use to build the custom function.It uses Data Services's built-in-function day_in_week().


    $L_Start and $L_End are the two input dates

    $L_Diff =  day_in_week($L_End) - day_in_week($L_Start) ;
    $L_Output = ((date_diff(  $L_End,$L_Start,'D')- $L_Diff) / 7 * 5)  + (decode(($L_Diff <5) ,$L_Diff,5))-decode ( day_in_week($L_End)-4 > 0 , day_in_week($L_End)-4 ,0)%5;
    return($L_Output);
    
    
    Add comment
    10|10000 characters needed characters exceeded