Number of days between two dates excluding weekends

[Former Member]

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

[mageshwaran_subramanian]

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

[former_member187605]

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)