5 Replies Latest reply: Feb 6, 2018 10:13 AM by omar bensalem

# Store working days between begin and end dates in seperate variables

Hi folks ( stalwar1)

I have some questions and hope u'll help me solving them.

Here we go :

Imagine I have 2 dates:

BeginDate and EndDate

I want to have all the Dates between this Begin and EndDate that are Working Days and store each day in a variable:

Example:

BeginDate : 31/01/2018

EndDate : 06/02/2018

List of working days btwn these 2 days:

31/01/2018, 01/02/2018 , 02/02/2018, 05/02/2018, 06/02/2018

Thus, I want to have sthing like this:

vD1: 31/01/2018

vD2: 01/02/2018

vD3 : 02/02/2018

vD4: 05/02/2018

vD5: 06/02/2018

Is this possible? If so, any idea in how can I do such a thing?

• ###### Re: Store working days between begin and end dates in seperate variables

May be this

Table:

Where not Match(WeekDay, 'Sat', 'Sun');

LOAD Date(BeginDate + IterNo() - 1) as Date,

WeekDay(BeginDate + IterNo() - 1) as WeekDay

While BeginDate + IterNo() - 1 <= EndDate;

BeginDate, EndDate

31/01/2018, 06/02/2018

];

FOR i = 1 to FieldValueCount('Date')

LET vD\$(i) = FieldValue('Date', \$(i));

NEXT;

• ###### Re: Store working days between begin and end dates in seperate variables

I'll always learn sthing from u:

I've done this:

Let varMinDate = num(date#('30/01/2018'));

Let varMaxDate = num(date#('06/02/2018'));

TempCalendar:

\$(varMinDate) + Iterno()-1 As Num,

Date(\$(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

Dates:

rowno() as row, TempDate AS Date

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

for a=0 to FieldValueCount('row')-1

let Day\$(a) = Peek('Date',\$(a), 'Dates');

next a;

But didn't know how to exclude weekends !

• ###### Re: Store working days between begin and end dates in seperate variables

I've tried ur solution Sunny, but I see that there variable created even for Staurday and sunday (02/02/2018 and 03/02/2018)

• ###### Re: Store working days between begin and end dates in seperate variables

Table:

Where not Match(WeekDay, 'Sat', 'Sun');

LOAD Date(BeginDate + IterNo() - 1) as Date,

WeekDay(BeginDate + IterNo() - 1) as WeekDay

While BeginDate + IterNo() - 1 <= EndDate;

BeginDate, EndDate

31/01/2018, 06/02/2018

];

FOR i = 1 to FieldValueCount('Date')

LET vD\$(i) = FieldValue('Date', \$(i));

NEXT;

• ###### Re: Store working days between begin and end dates in seperate variables

My bad.. WeekDays are written in french by default for me.. that's why !