Set a date and exclude weekends and vacation days

[EdouardVH]

Hello,

I have an application which aims to set a task end date.

To define this date I must enter the number of days and then exclude weekends and holidays which are registered in a sharepoint list.

I have set up two calculation formulas but it does not work I would like your help.

Formula 1 :

the first datapicker is the departure date and the second is the calculated date 

Spoiler

With({startDate:DatePicker_1.SelectedDate;daysToAdd:Value(TextBox_75.Text)};
DateAdd(startDate;daysToAdd)+RoundDown(daysToAdd/5;0)*2+
Switch(Weekday(startDate;StartOfWeek.Monday);5;
If(Mod(daysToAdd;5)>0;2;0);4;
If(Mod(daysToAdd;5)>1;2;0);3;
If(Mod(daysToAdd;5)>2;2;0);2;
If(Mod(daysToAdd;5)>3;2;0);1;
If(Mod(daysToAdd;5)>4;2;0))
+
CountIf(Holiday;HolidayDate>=startDate;HolidayDate<=startDate))

Formula 2 :

the first datapicker is the departure date and the second is the calculated date 

Spoiler

DateAdd(DatePicker_1.SelectedDate;
Value(TextBox_66.Text) +
RoundDown(Value(TextBox_66.Text) / 5; 0)*2+
Switch(Weekday(DatePicker_1SelectedDate);5;If(Mod( Value(TextBox_66.Text) ; 5)>0;2;0);
4;If(Mod( Value(TextBox_66.Text) ; 5)>1;2;0);
3;If(Mod( Value(TextBox_66.Text) ; 5)>2;2;0);
2;If(Mod( Value(TextBox_66.Text) ; 5)>3;2;0);
1;If(Mod( Value(TextBox_66.Text) ; 5)>4;2;0))
)+

CountIf(
Holiday;
HolidayDate >= DateAdd(DatePicker_1.SelectedDate;
Value(TextBox_66.Text) +
RoundDown(Value(TextBox_66.Text) / 5; 0)*2+
Switch(Weekday(DatePicker_1.SelectedDate);5;If(Mod( Value(TextBox_66.Text) ; 5)>0;2;0);
4;If(Mod( Value(TextBox_66.Text) ; 5)>1;2;0);
3;If(Mod( Value(TextBox_66.Text) ; 5)>2;2;0);
2;If(Mod( Value(TextBox_66.Text) ; 5)>3;2;0);
1;If(Mod( Value(TextBox_66.Text) ; 5)>4;2;0))
);
HolidayDate <= DateAdd(DatePicker_1.SelectedDate;
Value(TextBox_66.Text) +
RoundDown(Value(TextBox_66.Text) / 5; 0)*2+
Switch(Weekday(DatePicker_1.SelectedDate);5;If(Mod( Value(TextBox_66.Text) ; 5)>0;2;0);
4;If(Mod( Value(TextBox_66.Text) ; 5)>1;2;0);
3;If(Mod( Value(TextBox_66.Text) ; 5)>2;2;0);
2;If(Mod( Value(TextBox_66.Text) ; 5)>3;2;0);
1;If(Mod( Value(TextBox_66.Text) ; 5)>4;2;0))
)
)

The week starts on a Monday and the sharepoint list which includes vacation days is HolidayDate

[Ramole]

Hi @EdouardVH 

Do you want to exclude weekend ?
have look this post more https://powerusers.microsoft.com/t5/Building-Power-Apps/Exclude-the-weekends/m-p/901790 

[EdouardVH]

Hi , @Ramole 

yes I would like to exclude the weekends and the days present in my sharepoint list.

The topics I find explain how to exclude weekends and days between two dates.

except that in my case I have only one departure date to which I add a number of days.

[WarrenBelz]

Hi @EdouardVH ,

You might look at this excellent article from @timl 

[EdouardVH]

hi @WarrenBelz ,

Thank you for your help, !!

Yes I have read and tested, but it does not work.

Take the example if I select November 30, 2021 and I want to put -18 days I fall on the weekend. November 6, 2021

[WarrenBelz]

Hi @EdouardVH ,

I don't think it was meant to have negative values.

[EdouardVH]

Thanks @WarrenBelz ,

I would have liked to find a solution, it would have been my Christmas present 😂

[WarrenBelz]

Hi @EdouardVH ,

I will tag the author @timl here, but he will probably be offline due to his timezone.

[timl]

Hi @WarrenBelz - Thanks for tagging me and for linking my post. You're correct - most of these solutions are not designed to work with negative values.

The general technique relies on us calculating the number of working days based on full weeks, and to make an adjustment based on the number of partial days. The places where we 'hardcode' these additions would need to be subtractions in the case of where we want to subtract working days. Where we calculate the partial days, rather than calculate the number of days to the weekend, we need to calculate the number of days from the weekend since we're now counting backwards.

@EdouardVH - To cope with negative values, my initial thought is to base the calculation on the absolute positive value, and to introduce if statements to account for the negative variations. The code would look like this:

With({weekDaysToAdd: -18},
With(
    { weekDaysToAddAbs: Abs(weekDaysToAdd),
      startDate:Date(2021,11,30) },
    With(
        {
            fullWeeks: RoundDown(weekDaysToAddAbs / 5, 0),
            remainingDays: Mod(weekDaysToAddAbs, 5),
            daysToWeekend: 
            If(weekDaysToAdd<0,Weekday(startDate, StartOfWeek.Monday)
            ,5- Weekday(startDate, StartOfWeek.Monday))            
        },
        With(
            { daysToAdd: fullWeeks * 7 + remainingDays + If(remainingDays > daysToWeekend, 2, 0) },
            DateAdd(startDate, If(weekDaysToAdd<0,-1,1)*daysToAdd )))
   )
)

This produces the following result:

This verifies the same formula against your original question of +45 days.

Because @CarlosFigueira 's formula was neater, it was easier to add these variations to his formula so credit goes to Carlos.

To make the adjustment for vacation days, you can count the number of days and add that as a positive value to weekDaysToAddAbs.

[EdouardVH]

Hi @WarrenBelz  ,  @timl , 

Sorry for the delay, Thank you very much for your help with my project.

@timl  can you confirm to me that adding the number of days to weekDaysToAdAbs is correct? 

Spoiler

With({weekDaysToAdd: -18},
With(
{ weekDaysToAddAbs: Abs(weekDaysToAdd),
startDate:Date(2021,11,30) },
With(
{
fullWeeks: RoundDown(weekDaysToAddAbs / 5, 0),
remainingDays: Mod(weekDaysToAddAbs, 5),
daysToWeekend:
If(weekDaysToAdd<0,Weekday(startDate, StartOfWeek.Monday)
,5- Weekday(startDate, StartOfWeek.Monday))
},
With(
{ daysToAdd: fullWeeks * 7 + remainingDays + If(remainingDays > daysToWeekend, 2, 0) },
DateAdd(startDate, If(weekDaysToAdd<0,-1,1)*daysToAdd )))
) + CountIf(Holiday;HolidayDate>=startDate;HolidayDate<=startDate) )
)

thank you very much