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.

Thanks & Regards,

Balamurugan G

10|10000 characters needed characters exceeded

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

10|10000 characters needed characters exceeded
• 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);

```