Date Calculations not working right

[scottmcnitt]

I have a start date picker and need to know what the date would be 15 days after only counting business days and not counting holidays as part of the 15.  I have a text label with the following code

For example: i set the start date for 12/15/2022 and there is 2 holidays in its path Christmas and New Years. i have the holidays in a collection called collectHoliday the desired date should be 1/9/2023 what its returning is 1/6/2023. I am stuck what am i missing here.

collectHoliday

ClearCollect(
collectHoliday,
{
HolidayName: "Thanksgiving",
HolidayDate: "11/25/2022 12:00 AM"
},
{
HolidayName: "Christmas",
HolidayDate: "12/26/2022 12:00 AM"
},
{
HolidayName: "New Years",
HolidayDate: "01/02/2023 12:00 AM"
}
);

Text Label for results:

Text(With({ startDate:DateValue(DatePicker.SelectedDate),endDate:DateValue(DateAdd(DatePicker.SelectedDate, 15)),
daysToAdd: 15
},
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(collectHoliday, DateValue(HolidayDate) >= startDate, DateValue(HolidayDate) <= endDate)
),ShortDate)

[iAm_ManCat · Accepted solution]

Heya, I did this slightly differently, added my code below, I got the 9th but I think it's because at the end you are checking if holidays are less than equal to endDate, but that endDate is only within the first 15 Actual days not weekdays so New Years doesn't fall within that range and it doesn't account for the fact that within the 'extra' days like weekends and holidays that end up getting added to the original 15 you might have more weekends or holidays that need the date further extended

With({OriginalDaysToAdd: 15},
 With({OriginalWeekendHolidayCount: 
  CountIf(
   ForAll(
    Sequence(OriginalDaysToAdd,1,1),
    If( //If sat/sun
       Weekday(DateAdd(DatePicker2.SelectedDate,Value)) = 1 ||
       Weekday(DateAdd(DatePicker2.SelectedDate,Value)) = 7 ||
       ///OR holiday, if holiday falls on sat/sun will still only count as 1
       DateAdd(DatePicker2.SelectedDate,Value) in colHoliday.HolidayDate,
       1,0
    )
   ),
   Value=1
  )},
   //Get dates after the OriginalDaysToAdd that may have been
   // a holiday/weekend instead of just adding without checking
   With({SecondWeekendHolidayCount: 
    CountIf(
     ForAll(Sequence(OriginalWeekendHolidayCount,1,1),
      If( //sat or sun following those initial 15 days
         Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+Value))) = 1 ||
         Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+Value))) = 7 ||
         DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+Value)) in colHoliday.HolidayDate,
         1,0
      )
     ),
     Value=1
    )},
     With({ThirdWeekendHolidayCount: CountIf(
      ForAll(Sequence(SecondWeekendHolidayCount,1,1),
       If(
          Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+Value))) = 1 || 
          Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+Value))) = 7 ||
          DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+Value)) in colHoliday.HolidayDate,
          1,0
       )
      ),
      Value=1
     )},
     With({FourthWeekendHolidayCount: CountIf(
       ForAll(Sequence(ThirdWeekendHolidayCount,1,1),
        If(
           Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+Value))) = 1 || 
           Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+Value))) = 7 ||
           DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+Value)) in colHoliday.HolidayDate,
           1,0
        )
       ),
       Value=1
      )},
        With({TotalDaysToAdd: OriginalDaysToAdd +OriginalWeekendHolidayCount + SecondWeekendHolidayCount + ThirdWeekendHolidayCount + FourthWeekendHolidayCount},
         //FinalDate
         DateAdd(DatePicker2.SelectedDate, TotalDaysToAdd)
        )
    )
   )
  )
 )
)

ClearCollect(
colHoliday,
{
   HolidayName: "Thanksgiving",
   HolidayDate: DateValue("11/25/2022", "en-US")
},
{
   HolidayName: "Christmas",
   HolidayDate: DateValue("12/26/2022", "en-US")
},
{
   HolidayName: "New Years",
   HolidayDate: DateValue("01/02/2023", "en-US")
}
);

Cheers,

Sancho

[iAm_ManCat]

I've attached a POC .msapp that you can open from the editor to play around and test what I made

Cheers,

Sancho

[WarrenBelz]

Hi @scottmcnitt 

Firstly, for collectHolidays, I did this

ClearCollect(
   collectHoliday,
   {
      HolidayName: "Thanksgiving",
      HolidayDate: DateValue("11/25/2022")
   },
   {
      HolidayName: "Christmas",
      HolidayDate: DateValue("12/26/2022")
   },
   {
      HolidayName: "New Years",
      HolidayDate: DateValue("01/02/2023")
   }
);

I did not use your structure, but modified one I use, but this should work - NOTE where you have 15 (days) hard-coded, I have a Text Box txHolidayNo to enter it into.

With(
   {
      wStart: DatePicker.SelectedDate,
      wDays: Value(txHolidayNo.Text)
   },
   With(
      {
         wBase: DateAdd(
            wStart,
            Value(wDays) + RoundDown(
               (Weekday(
                  wStart,
                  Monday
               ) + wDays) / 5,
               0
            ) * 2,
            Days
         )
      },
      With(
         {
            wAddHols: 
            DateAdd(
               wBase,
               CountIf(
                  collectHoliday,
                  HolidayDate >= wStart && HolidayDate <= wBase
               ),
               Days
            )
         },
         Text(
            DateAdd(
               wAddHols,
               If(
                  Weekday(
                     wAddHols,
                     Monday
                  ) = 6,
                  2
               )
            ),
            ShortDate
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[WarrenBelz]

Hi @scottmcnitt ,

Just out of interest, did you try my code, which allowed for a flexible day amount and is a different structure to the app from @iAm_ManCat . I tested it and it worked as expected.

[scottmcnitt]

It did work as well.  Thank you.  

[scottmcnitt]

Found one edge case issue.  On a 20 day count the end date lands on a holiday.  i created a text label and added the code on changed to 20 days from 15.  and on 12/15/2022 20 days out lands on 1/16/2023 which i have in there for Dr Kings bday holiday. so it should not be able to have the end date on a holiday

[iAm_ManCat]

Just found a bug in the code I gave, there were still some hardcoded 15's in there, instead of pointing to the variable, so that's why it fluffed the date up, revised code below here:

With({OriginalDaysToAdd: Value(txHolidayNo.Text)},
 With({OriginalWeekendHolidayCount: 
  CountIf(
   ForAll(
    Sequence(OriginalDaysToAdd,1,1),
    If( //If sat/sun
       Weekday(DateAdd(DatePicker2.SelectedDate,Value)) = 1 ||
       Weekday(DateAdd(DatePicker2.SelectedDate,Value)) = 7 ||
       ///OR holiday, if holiday falls on sat/sun will still only count as 1
       DateAdd(DatePicker2.SelectedDate,Value) in colHoliday.HolidayDate,
       1,0
    )
   ),
   Value=1
  )},
   //Get dates after the OriginalDaysToAdd that may have been
   // a holiday/weekend instead of just adding without checking
   With({SecondWeekendHolidayCount: 
    CountIf(
     ForAll(Sequence(OriginalWeekendHolidayCount,1,1),
      If( //sat or sun following those initial 15 days
         Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+Value))) = 1 ||
         Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+Value))) = 7 ||
         DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+Value)) in colHoliday.HolidayDate,
         1,0
      )
     ),
     Value=1
    )},
     With({ThirdWeekendHolidayCount: CountIf(
      ForAll(Sequence(SecondWeekendHolidayCount,1,1),
       If(
          Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+Value))) = 1 || 
          Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+Value))) = 7 ||
          DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+Value)) in colHoliday.HolidayDate,
          1,0
       )
      ),
      Value=1
     )},
     With({FourthWeekendHolidayCount: CountIf(
       ForAll(Sequence(ThirdWeekendHolidayCount,1,1),
        If(
           Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+Value))) = 1 || 
           Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+Value))) = 7 ||
           DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+Value)) in colHoliday.HolidayDate,
           1,0
        )
       ),
       Value=1
      )},
        With({FifthWeekendHolidayCount: CountIf(
            ForAll(Sequence(FourthWeekendHolidayCount,1,1),
                If(
                Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+Value))) = 1 || 
                Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+Value))) = 7 ||
                DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+Value)) in colHoliday.HolidayDate,
                1,0
                )
            ),
            Value=1
            )},
            With({SixthWeekendHolidayCount: CountIf(
            ForAll(Sequence(FifthWeekendHolidayCount,1,1),
                If(
                Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+Value))) = 1 || 
                Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+Value))) = 7 ||
                DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+Value)) in colHoliday.HolidayDate,
                1,0
                )
            ),
            Value=1
            )},
            With({SeventhWeekendHolidayCount: CountIf(
            ForAll(Sequence(SixthWeekendHolidayCount,1,1),
                If(
                Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+FifthWeekendHolidayCount+Value))) = 1 || 
                Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+FifthWeekendHolidayCount+Value))) = 7 ||
                DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+FifthWeekendHolidayCount+Value)) in colHoliday.HolidayDate,
                1,0
                )
            ),
            Value=1
            )},
            With({EighthWeekendHolidayCount: CountIf(
            ForAll(Sequence(SeventhWeekendHolidayCount,1,1),
                If(
                Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+FifthWeekendHolidayCount+SixthWeekendHolidayCount+Value))) = 1 || 
                Weekday(DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+FifthWeekendHolidayCount+SixthWeekendHolidayCount+Value))) = 7 ||
                DateAdd(DatePicker2.SelectedDate,(OriginalDaysToAdd+OriginalWeekendHolidayCount+SecondWeekendHolidayCount+ThirdWeekendHolidayCount+FourthWeekendHolidayCount+FifthWeekendHolidayCount+SixthWeekendHolidayCount+Value)) in colHoliday.HolidayDate,
                1,0
                )
            ),
            Value=1
            )},
        With({TotalDaysToAdd: OriginalDaysToAdd +OriginalWeekendHolidayCount + SecondWeekendHolidayCount + ThirdWeekendHolidayCount + FourthWeekendHolidayCount + FifthWeekendHolidayCount + SixthWeekendHolidayCount + SeventhWeekendHolidayCount + EighthWeekendHolidayCount},
            //FinalDate
            DateAdd(DatePicker2.SelectedDate, TotalDaysToAdd)
            )
          )
        )
       )
      )
    )
   )
  )
 )
)

I tested out all +days from 1-40 added and it validated correctly now.

Attached a more recent POC app 🙂

[scottmcnitt]

Heres an additional issue im trying to take what you have on here and work it in a way that i have an additional text label that lets say is set for 2 months.  But that would include weekends and holidays with the only caveat that the final date cannot land on a weekend date or holiday date and it would round up to the next business day.

for example 12/15/2022 is the start date the label is set for 1 Month.  the end date "should" be 1/17/2023 because 1/16/2023 is a holiday for us and 1/15/2023 is a weekend.  If that makes sense.