10 Replies Latest reply: Apr 5, 2016 5:28 AM by vijaykrishnamraju vegesna

# Networkdays function to change non working days from saturday-sunday to thursday-friday

Hi Folks,

I have a requirement where we need to calculate the no of days between start date and end data.

We can use the

#### networkdays( start:date, end_date {, holiday} )

but this function automatically excludes saturday and sunday.

What i need is to exclude Thurday and Friday as non working days and include the saturday and sunday as working day while doing the calculation.

Our client working days are Sat-Wed  and Thu-Fri as weekOff.

So how can i use an logic to calculate the exact no of days.

Regards

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

Hi

Try like this,

Load *, if( WeekDay(DateField) = 'Thu' or WeekDay(DateField) = 'Fri', 0, 1) as Flag from tabkename;

Then Use count(Flag) as expression. You can achieve your requirement..

Hope it helps

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

If you only have dates for start and end date, you will first need to somehow create a date record for each day in your period. If you could calculate the networkdays in the script, you can do it like this:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41160,41162';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

9/1/12 11:08 AM,9/8/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM

];

// Create a record per day in your periods

TMP:

Date(daystart(DT1)+iterno()-1) as Date

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

// calculate the sum of days in your periods, that are not a Thu or Fri, or part of your Holidays (set variable vHol)

ID,

count(Date) as Networkdays

Resident TMP where (WeekDay(Date)<3 or WeekDay(Date)>4) and not match(Date,\$(vHol)) group by ID;

drop table TMP;

The Weekday() function returns 3 for Thursday and 4 for a Friday.

Hope this helps,

Stefan

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

Hi Stefan,

Thanks for the promp response to my query.

The solution provided by you was easy to understand and implement.

Regards,

Vasim

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

Hi,

as i see it it works but it creates raws for each day in between the dates in each row of the source table.

if my source table is big and in avg i have 5-10 days diff in each row it means ill need to create a table 5-10 times larger.

this might be a problem

any other ways?

i need networkdays function to give sunday-thursday as working days (excluding friday and saterday)

Wizardo

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

Maybe you could use an approach like this:

count(distinct if(match(weekday(Date), 0,1,2,3,6), Date))

- Marcus

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

yes ofcourse thats what i did

but that doesnt solve the problem of the large table case

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

It was meant as calculation within the gui. Further you didn't need create flags or calculations over the fact-table - better is to use a master-calendar.

- Marcus

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

what will the master-calendar help here?

i have a fact with 2 date in each row and i need to calculate the working days between these dates.

i see no way to use the master-calendar in this scenario

normally id use the function NETWORKDAYS() but this functions assumes week where Monday is first day of the week and something that can work with weeks where Sunday is first day and Friday and Saturday are non working days.

and

##### swuehl

solution was the best i have found so far

Wizardo

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

I don't know what do you want to do with these networkdays-values. If you don't calculate these values further within the script it could be better to perform such calculation on a small amount of data (restricted by selections) within the gui.

Nevertheless you could create an expression with a main-logic like this:

(enddate - startdate) - floor((enddate - startdate) / 7)

with an additional considering from the weekday from the startdate and enddate and a little bit fine-tuning.

I have built such expression some time ago but actually couldn't find it. Good luck.

- Marcus

• ###### Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

Best way is take a primary key field from your data model tables..

And write the expression like

if(count(primarykey)>0 ,count(distinct date))

then it will display the working days of your customer data regardless sun,wed...etc