New Date Calculation issue

[scottmcnitt]

I am trying to count 180 days from now with the issue that the end date calculation cannot land on a weekend or a holiday.  i have a collection for my holidays collectHoliday im struggling with the syntax for not letting land on the weekend or holiday.  im trying right now just with it not landing a holiday but im missing a lot i know:

Text Label (Text)

------------------

With({OriginalDaysToAdd: 180},
With({HolidayCount:
CountIf(
ForAll(
Sequence(OriginalDaysToAdd,1,1),
If(
Text(DateAdd(DatePicker.SelectedDate +OriginalDaysToAdd = DateValue(collectHoliday.HolidayDate)ShortDate),
1,0
)}
)
)

[scottmcnitt]

Its another label that lets say 10 Business Days.  this would add 10 days that are only M-F and if a holiday is in there somewhere it will add another day to make up for that. and it cannot land on a weekend or a holiday.

So 10 business days from today would count Fri, Mon, Tue, Wed, Thur, Fri, Mon, Tue, Wed, Thur should be 1/20

[TheRobRush]

Here you go, just change the 10 near the end to the number of business days

Text(
Last(
FirstN(
Sort(
Filter(
ForAll(
        Sequence(50,1,1) As DayCount,
        With(
             {targetDate:DateAdd('Day 1_2'.SelectedDate,DayCount.Value,Days)},
             {dateOf:targetDate})), 
             Not(dateOf in HolidayCelebrate)),
             dateOf,Ascending), 10)).dateOf, ShortDate)

[scottmcnitt]

Everything works except the very last count which is 60 Business Days.  it is showing 2/24 i know that to be WAY off

Text(
Last(
FirstN(
Sort(
Filter(
ForAll(
Sequence(50,1,1) As DayCount,
With(
{targetDate:DateAdd(DatePicker.SelectedDate,DayCount.Value,Days)},
{dateOf:targetDate})),
Not(dateOf in HolidayCelebrate)),
dateOf,Ascending), 60)).dateOf, ShortDate)

[TheRobRush]

Haha yaha thats on me, was creating this for 15 and didn't bother reading further down.

Change sequence(50 to something like sequence(200 and that should give you more room for dates further off

[scottmcnitt]

I changed the sequence to (100,1,1) As DayCount,  

Seemed to fix it.

[scottmcnitt]

The finished product.  There are some additional features i was supposed to add like if the date adjusts to a monday and or in the business days if there holidays inline that the date gets highlighted yellow but that might be down the road.  I do want to add an additional label to each day count that shows what day of the week that specific date is, i will look into that as well

Thank you for all your help, i really appreciate it.

[TheRobRush]

Home on my cellphone now so there may be some syntax issues, but for the day of week thing just set label.next to each of them.and makes its text something like

Switch(
     WeekDay(LabelNameYouAreChecking.Value),
     1, "Su",
     2, "Mo",
     3, "Tu",
     4, "We",
     5, "Th",
     6, "Fr",
     "Sa")

Not sure I understand the other two requests, maybe you can draw up an example of them for me

[scottmcnitt]

Days of the work worked perfect.  For the highlighting part its basically if the end date was altered by moving to a monday because it landed on a weekend or if it counts a holiday in its range then it would change the fill color

for this example 1/16/2023 is a holiday so for my 10 Day count it is showing 1/17/2023 so it would highlight

Our old web system did that and im sure someone requested that long ago

[TheRobRush]

Ok I understand, doable, will hash it out for you when I am in Office Again, but the general idea of it (if youd like to start playing with it on your own) would be in the fill color.of that label put something like

For 10 days

If(dateadd(datepicker.selecteddate,10,Days)=self.value, color if it was not moved, color.if it was moved)

[scottmcnitt]

I will work on it once i get all the Days of Week done. SOOOOO many Labels lol.