Holidays Condition for Formula

[syhrh]

Hi, I have this label that will be visible if employees do not apply for Annual Leave 3 days in advance. 3 days means Monday, Tuesday, and Wednesday. The employee can apply on Thursday.

         

I set the date picker as 29 August and 31 August is a holiday so when employees apply for Annual leave, their StartDate is 29 August or 30 August or 31 August or 1 September, the label should be visible as 31 August doesn't count in the 3 days requirement. As you can see from the picture below, the label is not visible when it's 1 September while the label should only be invisible on 2 September

        

These are the formulas I use.

//App.OnStart
Set(checkCondition, LookUp('Leave Policy', Title="AnnualLeave"));
Set(
    checkCondition, 
    First(
        SortByColumns(
            Filter(
                'Leave Policy', 
                Title = "AnnualLeave" && 'Effective Date' <= DatePicker1.SelectedDate
            ), 
        "EffectiveDate", Descending)
    )
);
Set(checkCondition2, checkCondition.Days);

//DataCardValue2.OnChange
//DataCardValue2 is Start Date
//DataCardValue17 is End Date
Set(
    VarDateDiff,
    RoundDown(
        DateDiff(
            DatePicker1.SelectedDate,
            DataCardValue2.SelectedDate,
            Days
        ) / 7,
        0
    ) * 5 + Mod(
        5 + Weekday(DataCardValue2.SelectedDate) - Weekday(DatePicker1.SelectedDate),
        5
    ) - CountIf(Holidays, StartDate >= DataCardValue2.SelectedDate, StartDate <= DataCardValue17.SelectedDate)
);
If(
    Weekday(DataCardValue2.SelectedDate) = 7 || Weekday(DataCardValue2.SelectedDate) = 1,
    Set(
        VarDateDiff,
        0
    );
    Set(
        VarWeekEndError,
        true
    ),
    Set(
        VarWeekEndError,
        false
    )
);
Set(
    VarNegativeDateError,
    If(
        DatePicker1.SelectedDate > DataCardValue2.SelectedDate,
        true,
        false
    )
);
Set(
    VarAllowHoliday,
    If(
        VarDateDiff >= Value(checkCondition2),
        true,
        false
    )
);

I tried to change the VarDateDiff >= Value(CheckCondition2), into VarDate > Value(CheckCondition2). It works for holidays only but if there are no holidays then it exceeds the 3 days requirement.

How to fix this issue?

Thank you.

[Anonymous_Hippo]

Hello @syhrh ,

Could you please explain the required logic on how  to handle holidays. lets say that Today's Date is August 15 th and I want to request vacation from 18th August but there is a holiday on 17th August. Should we only count working days when making the 3 days calculation or should we ignore the holiday in between. Thank you 🙂

[syhrh]

Hi @Anonymous_Hippo, the 3 days calculation should only count working days so the 17 August isn't included. So you cannot apply for leave from 18 August because it doesn't meet the 3-day requirement and you can only apply on 19 August. 

Thank you.

[Anonymous_Hippo]

Clear now ! @syhrh  Could you also please explain the logic of what you are trying to do with checkcondition2. also could you share a screenshot of that sharepoint list. I think the second formula is good  - the problem child is checkcondition2 

Thanks 

[syhrh]

Hi @Anonymous_Hippo, the checkcondition2 is to check the days based on the Leave Policy list. In case admin wants to add another policy for the Annual Leave leave application 

[Anonymous_Hippo]

@syhrh 

Looks like you are calculating the holidays part incorrectly . In your current formula you are considering holidays that fall in between the start date and end date only but we need to consider holidays falling in between Today and the start date only.

Can you try both these formula instead - both can be copy pasted directly (i have changed the names to match your variables) 

Start date OnChange property

Set(
    VarDateDiff, //This variable gives the number of days from today to start date of planned leave- excluding holidays and weekends
    RoundDown(
        DateDiff(
            Today(),
            DataCardValue2.SelectedDate,
            Days
        ) / 7,
        0
    ) * 5 + Mod(
        5 + Weekday(DataCardValue2.SelectedDate) - Weekday(Today()),
        5
    )- CountIf(Holidays, StartDate >= Today(), StartDate <= DataCardValue2.SelectedDate)// this counts the # of holidays falling inbetween Today and Start date
);
If(
    IsBlank(DataCardValue17.SelectedDate),//if Start date is not set the show error
    Set(
        VarDateDiff,
        0
    );
    Set(
        VarStartDatenotSetError,
        true
    ),
    Set(
        VarStartDatenotSetError,
        false
    )
);
If(
    Weekday(DataCardValue2.SelectedDate) = 7 || Weekday(DataCardValue2.SelectedDate) = 1,
    Set(
        VarDateDiff,
        0
    );
    Set(
        VarWeekEndError,
        true
    ),
    Set(
        VarWeekEndError,
        false
    )
);
Set(
    VarNegativeDateError,
    If(
        Today() > DataCardValue2.SelectedDate,
        true,
        false
    )
);
Set(
    VarAllowHoliday,
    If(
        VarDateDiff >= Value(checkCondition2),
        true,
        false
    )
);

OnStart

//App.OnStart
Set(checkCondition, LookUp('Leave Policy', Title="AnnualLeave"));
Set(
    checkCondition, 
    First(
        SortByColumns(
            Filter(
                'Leave Policy', 
                Title = "AnnualLeave" && 'Effective Date' <= DatePicker1.SelectedDate
            ), 
        "EffectiveDate", Descending)
    )
);
Set(checkCondition2, checkCondition.Days);

Error Text

If(VarStartDatenotSetError,"Start date needs to be set to allow leave calculation",If(VarWeekEndError,"Start Date cannot be on a weekend",If(VarNegativeDateError,"Start Date cannot be less than or equal to Today",If(!VarAllowHoliday,"Cannot apply for annual leave as your start date is less than "&Value(checkCondition2)&" days from today (Excluding Weekdays)"))))

Cheers !

[syhrh]

Hi @Anonymous_Hippo,

I've tested the formula 3 times, each on a different holiday using a date picker just like you showed me in a previous post. The first test works fine for the 3-day requirement. In the second test, the label will only disappear when it's 4 days for the StartDate. In the third test, the label will only disappear when it's 6 days for the StartDate. It keeps increasing over the days. How to fix it?

Thank you.

[Anonymous_Hippo · Accepted solution]

Dear @syhrh ,

Could you please provide each of those 3 examples and the holidays you had in between. I can make a similar test case - this will help me figure out what is wrong.

I made three test cases

Lets assume today's date is 24rth august

1. Start Date is 30th august and End date is 5th September. WE have a company holiday on 25th August. - Success
   • I got the error successfully
   • 
2. Start Date is 31st August and end date is 5th Sept. we have the same holiday fallin on 25th august. Since our difference is 3 we do not see any error now- Success
   • 
3. Start Date is 31st August and end date is 23rd Sept. We have holidays on 25th Aug, 29 Aug, 30th Aug. Therefore holiday can be requested only on 1st sept - Success
   • 

Here make sure that you are using the correct checkcondition2 value here i am using 3 days.

i updated the formulas to catch a couple more errors

//StartDate OnChnage
Set(
    VarDateDiff, //This variable gives the number of days from today to start date of planned leave- excluding holidays and weekends
    RoundDown(
        DateDiff(
            Today(),
            DataCardValue2.SelectedDate,
            Days
        ) / 7,
        0
    ) * 5 + Mod(
        5 + Weekday(DataCardValue2.SelectedDate) - Weekday(Today()),
        5
    )- CountIf(Holidays, StartDate >= Today(), StartDate <= DataCardValue2.SelectedDate)// this counts the # of holidays falling inbetween Today and Start date
);
If(
    IsBlank(DataCardValue17.SelectedDate),//if End date is not set the show error
    Set(
        VarDateDiff,
        0
    );
    Set(
        VarEndDatenotSetError,
        true
    ),
    Set(
        VarEndDatenotSetError,
        false
    )
);
If(DataCardValue2.SelectedDate >DataCardValue17.SelectedDate,Set(VarStartDateLess,true),Set(VarStartDateLess,false));
If(DataCardValue17.SelectedDate < DataCardValue2.SelectedDate,Set(VarEndDateLess,true),Set(VarEndDateLess,false));

If(
    Weekday(DataCardValue2.SelectedDate) = 7 || Weekday(DataCardValue2.SelectedDate) = 1,
    Set(
        VarDateDiff,
        0
    );
    Set(
        VarWeekEndError,
        true
    ),
    Set(
        VarWeekEndError,
        false
    )
);
If(!IsBlank(LookUp(Holidays,DataCardValue2.SelectedDate=StartDate)) || !IsBlank(LookUp(Holidays,DataCardValue17.SelectedDate=StartDate)),Set(VarHolidayError,true),Set(VarHolidayError,false));
Set(
    VarNegativeDateError,
    If(
        Today() > DataCardValue2.SelectedDate,
        true,
        false
    )
);
Set(
    VarAllowHoliday,
    If(
        VarDateDiff >= Value(checkCondition2),
        true,
        false
    )
);

Error Text

If(VarEndDatenotSetError,"End date needs to be set to allow leave calculation",If(VarHolidayError,"Start Date or end Date cannot be on a holiday",If(VarEndDateLess,"End Date needs to be after Start Date",If(VarStartDateLess,"Start Date needs to be before end date", If(VarEndDatenotSetError,"End date needs to be set to allow leave calculation", If(VarWeekEndError,"Start Date cannot be on a weekend",If(VarNegativeDateError,"Start Date cannot be less than or equal to Today", If(!VarAllowHoliday,"Cannot apply for annual leave as your start date is less than " & Value(checkCondition2) &" days from today (Excluding Weekdays)"))))))))

Honestly this should work not sure why it is not working for you

[syhrh]

Hi @Anonymous_Hippo, it works! Thank you so much for your time and effort. I really appreciate it.