Exclude Weekends and Public holidays using one datefield

[#1]

Good day,

The question I asked earlier on was not so clear. Now I am simplifying it, I have a date field called LeaveDate and it contains Working days from Monday to Friday, Weekends and Holiday. So in my country if a holiday falls on a Sunday, then following day(i.e Monday) will be counted as a holiday. So in my attached spreadsheet, I have highlited the holidays and weekends in Yellow. So what I would like to exclude weekends and public holidays.

         

hope my question is clear.

Thanks

[#2]

Yes it was just given to us by the president, but under normal circumstances it's not a holiday. The holidays are 25th and 26th 

[raajaswin · Accepted solution]

LET vDatMi = Num(MakeDate(2015,1,1));

LET vDatMa = Num(MakeDate(2017,1,1));

CAL1:

LOAD

Date($(vDatMi) + IterNo()) AS  ALL_DATE

AUTOGENERATE 1

WHILE $(vDatMi)+IterNo() <= $(vDatMa);

HOLIDAY_WEEKEND:

LOAD

if(match(weekday(ALL_DATE),'Sat','Sun'),ALL_DATE) as Holidays

Resident CAL1;

Drop Table CAL1;

Concatenate

PUBLIC_HOLIDAY_LIST:

LOAD Holiday as Holidays

FROM

[Public Holidays.xlsx]

(ooxml, embedded labels, table is Sheet1);

LEAVE:

LOAD LeaveDate as Working_day

FROM

[Leave Data.xlsx]

(ooxml, embedded labels, table is Sheet1) where not Exists(Holidays,LeaveDate);

[#4]

Thank you Aswin, this is perfect.

[sunny_talwar]

Check this out

Script

Table:

LOAD Holiday,

  NewHoliday,

  Date(If(NewHoliday = Peek(NewHoliday), NewHoliday + 1, NewHoliday)) as NewHoliday2;

LOAD Holiday,

  Date(If(WeekDay(Holiday) = 'Sat', Holiday + 2, If(WeekDay(Holiday) = 'Sun', Holiday + 1, Holiday))) as NewHoliday;

LOAD * INLINE [

    Holiday

    1/1/2015

    3/4/2015

    3/21/2015

    3/22/2015

    4/27/2015

    5/1/2015

    6/4/2015

    6/16/2015

    8/9/2015

    8/10/2015

    9/24/2015

    12/16/2015

    12/25/2015

    12/26/2015

    12/27/2015

    1/1/2016

    3/21/2016

    3/25/2016

    3/28/2016

    4/27/2016

    5/1/2016

    5/2/2016

    6/16/2016

    8/9/2016

    9/24/2016

    9/25/2016

    12/16/2016

    12/25/2016

    12/26/2016

];

Calendar:

LOAD Date,

  Month(Date) as Month,

  Year(Date) as Year,

  MonthName(Date) as MonthYear,

  If(Match(WeekDay(Date), 'Sat', 'Sun'), 'Weekend',

  If(Exists(NewHoliday2, Date), 'Holiday')) as Flag1;

LOAD Date(MakeDate(2014, 12, 31) + IterNo()) as Date

AutoGenerate 1

While MakeDate(2014, 12, 31) + IterNo() <= MakeDate(2017, 1, 1);

DROP Table Table;

[sunny_talwar]

Is this perfect? Didn't you just say that 12/27/2016 should be a holiday?

I guess I might not have understood your requirement

[raajaswin]

Sunny,

The public holiday list has been attached by Moses in a different thread.

[sunny_talwar]

Oh I see.... but I am still confused about if 12/27/2016 is a holiday or not

[raajaswin]

Exclude Weekends and Public Holiday

It is not a holiday because it is not provided in the public holiday list 

[#10]

Hi Sunny,

I have included 27th manually on my Public holiday field for only this year but as indicated earlier on that under normal circumstances it's not a public holiday

[sunny_talwar]

My bad, you are right, I was looking at 2015