Logic Help

[vaishali_k]

Hi Community,

I have below data

Date

05/07/2018

06/07/2018

07/07/2018

08/07/2018

09/07/2018

10/07/2018

11/07/2018

12/07/2018

13/07/2018

14/07/2018

In this data 08/07/2018 is Sunday and  11/07/2018 is National holiday. I want new field which calculated like if date 05/07/2018 add date +5 and i will get Output is date 12/07/2018 that means in calculation leave sunday and holiday expected output show in below table

Date                                      Output

05/07/2018                          12/07/2018

06/07/2018                          13/07/2018

07/07/2018                           14/07/2018

08/07/2018                           14/07/2018

09/07/2018                           16/07/2018

10/07/2018                            17/07/2018

11/07/2018                             17/07/2018

Above logic is applied for Output = (Date+5) (Excluded Sunday and Holiday) how i will get this output please help me.

Thanks in Advance.

[m_woolf]

Look at LastWorkDate

[vaishali_k]

Explain in terms of above example will be very helpful for me.

[Anil_Babu_Samineni]

How come 7 will be 14?

[balabhaskarqlik]

May be, like this:

=NetWorkingDays(Date#('07/25/2018','MM/DD/YYYY'),Today(),$(=Concat(chr(39)&If(HOLIDAY='Y',Date)&Chr(39),',')))

Or Script ex:

MySales:

Load

Date(Date#(SalesDate, 'DD.MM.YYYY')) as SalesDate, Sales

Inline [

SalesDate, Sales

03.05.2018, 230

04.05.2018, 25

16.05.2018, 66

10.05.2018, 120

19.05.2018, 32

12.04.2018, 100

05.04.2018, 12

03.06.2018, 88

];

Sales:

Load *,

Month(SalesDate) as  MyMonth,

MonthName(SalesDate) as MyMonthYear,

if(Year(SalesDate) = Year(Today()) and Month(SalesDate) = Month(Today()),

NetWorkDays(MonthStart(Today()),(Today()),$(vPublHoliday)),NetWorkDays(MonthStart(SalesDate),MonthEnd(SalesDate),

$(vPublHoliday))) as MyWorkingDays

Resident MySales;

[vaishali_k]

As i mentioned earlier suppose order is placed on 7th July 2018 (On Saturday) i.e 8th July is Sunday and 11th July is holiday as per given data, (So skip Sunday and holiday).

so as per the logic (day+5) leaving Sunday and holiday 7th July's output is 14th July. Hope this is clear.