Need to Ignore Weekends and Holidays

[SatyaPaleti]

Hi Experts,

I need a help on FirstworkDate Issue.

Actually I have a Data for 2015 and 2016 Years. I want to Ignore Weekends(Saturday & Sunday) and Holidays.

No of working Days is 8.

I have used FirstWorkDate function but unable to get Expected Output. Could some of you guys can please help me to resolve this Issue

Here Attached file are Date's Available and Holiday List

Thank you,

Satya

[avinashelite]

try with Networkingdays() function ..

function:networkday

Networkdays with holidays

[trdandamudi]

Follow the below link:

Exclude holidays using Networkdays() in script

[Anonymous]

define your Holidays in a variable like

vHolidays

'01.01.2016','25.03.2016','27.03.2016','28.03.2016','01.05.2016','05.05.2016','15.05.2016','16.05.2016','26.05.2016','03.10.2016','25.12.2016','26.12.2016'

define your start and enddate

user networkdays

=NetWorkDays(vStartdate,vEnddate,$(vHolidays))

[florentina_doga]

try this

//citesc fisierul cu sarbatorile legale

holydays_t:

LOAD * inline [holydays

1/1/2016

2/2/2016

3/3/2016

7/1/2016]    ;

//concatenez toate sarbatorile legale

NoConcatenate

holydays:

load

    concat(distinct CHR(39)&holydays&CHR(39),',') as concat_holydays

resident holydays_t;

drop table holydays_t;

//creez o variabila cu toate sarbatorile legale concatenate

let v_holydays=peek('concat_holydays',0,'holydays');

drop table holydays;

//exit script;

//creez o tabela de timeline incepand cu 1/1/2016 pana la sfarsitul lunii curente

TempCalendar:

LOAD

    date('1/1/2016' + rowno() - 1,'DD-MM-YYYY') AS DATA

AUTOGENERATE 1 While '1/1/2016' + rowno() - 1< monthend(today())-1;

//la tabela de timeline adaus anul, luna si daca ziua respectiva este lucratoare sau nu (aici tinem cont si de sarbatorile legale)

NoConcatenate

pontaje_finale:

load

    *,   

    NetWorkDays(DATA,DATA,$(v_holydays)) as days_work;

load

    *,

    month(DATA) as luna,

    year(DATA) as an,

       'S'&ceil(Month(date(DATA,'MMM-YY') )/6) as Semestru,

    Year(date(DATA,'MMM-YY') )&'-S'&ceil(Month(date(DATA,'MMM-YY') )/6) as An_Semestru,

    'Q'&ceil(Month(date(DATA,'MMM-YY') )/3) as Trimestru,

    dual(Year(date(DATA,'MMM-YY'))&'-Q'&ceil(Month(date(DATA,'MMM-YY'))/3),num(QuarterStart(date(DATA,'MMM-YY')))) as An_Trim,

    date(makedate(year(DATA),month(DATA),'01'),'MMM-YYYY')  as AnLuna,

    floor(monthstart(date(makedate(year(DATA),month(DATA),'01'),'MMM-YY')))  as AnLuna_today,

    'W'&week(date(DATA,'MMM-YY')) as Saptamana,

    dual (year(DATA) &'-w'& week(date(DATA,'MMM-YY')) ,    num(weekstart(date(DATA,'MMM-YY')))) as AnSapt

resident TempCalendar;

drop table TempCalendar;

[SatyaPaleti]

Hi Linder

Network Days only gives No of days in between Start Date and End Date. But I need Particular Date

Thanks

Satya

[SatyaPaleti]

Hi Thirumula

Network Days only gives No of days in between Start Date and End Date. But I need Particular Date

Thanks

Satya

[SatyaPaleti]

Hi Avinash,

Network Days only gives No of days in between Start Date and End Date. But I need Particular Date

Thanks

Satya

[Anonymous]

then you can use firstworkdate function and include your Holidays as well

=firstworkdate(vEnddate,8,$(vHolidays))  means when the start should be when we Need 8 working days and want to be finished at enddate regarding holidays

or you use lastworkdate

=lastworkdate(vStartdate,8,$(vHolidays)) 

[SatyaPaleti]

Linder,

I have used same process But It's not getting output as expected. It's not ignoring Holidays