Can't get RemoveIf to work with multiple conditions

[ToRo]

Hey Experts,

I can't figure out how to use the RemoveIf function with multiple conditions. 

We have a collection with a date range that users can select from a calendar and it is filled like this:

ClearCollect(
    DateRange,
    AddColumns(
        FirstN(
            [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
            DateDiff(Calendar_1.StartDate, Calendar_1.EndDate, Days) + 1),
        "Day",
            "Day " & (Value + 1),
        "Date",
            DateAdd(Calendar_1.StartDate, Value, Days)
    ,"Type",selectedType.type,"Hours","8","Person",_curUser));

Now I want to remove the weekend days and also matching holidays stored in a SP List "HolidaysGermany". I use RemoveIf like the following:

RemoveIf(DateRange, Date in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

After removing it does look like this (for example the range 4/9/2020 - 4/14/2020):

The removal with weekday does work fine, but it does never remove the holidays (in this case 4/10/2020 & 4/13/2020). I belive it's a date format problem but I don't know how to fix it. The date in the SP list is a normal date column and it looks like (switching to date and time format doesn't help):

Do you have any suggestions? Any help is appreciated!

Thanks and best regards

[gabibalaban]

Hi @ToRo ,

If you watch carrefully on your DateRange collection values you'll see that your Date column have also a time attached. This means that you compare a Date (retrived from Sharepoint with 00:00 at time) with this value and of course its never been equals.

I have a hunch that your Calendar component returns a DateTimeValue for StartDate and EndDate.

Can you try:

RemoveIf(DateRange, DateValue(Text(Date,"dd.mm.yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

Hope it helps !

[ToRo]

Hi @gabibalaban ,

thanks for this suggestion. Unfortunately it does not work, still the same behaviour. I tried to set locales as well:

RemoveIf(DateRange, DateValue(Text(Date,"[$-de-DE]dd.mm.yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

and also:

RemoveIf(DateRange, DateValue(Text(Date,"[$-en-US]dd.mm.yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

I also tried following:

ForAll(
    DateRange,
    If( CountIf(HolidaysGermany, DateOfHoliday >= ThisRecord.Date, DateOfHoliday <= ThisRecord.Date) > 0 || Weekday(ThisRecord.Date) = 1 || Weekday(ThisRecord.Date) = 7, Remove(DateRange, ThisRecord);
);

But this does also not work, since I operate on the same datasource, that is used in the ForAll.

Do you have any other suggestion?

Thanks and best regards

[ToRo · Accepted solution]

Hey all,

after a lot of research and trying to change formats, I came upon this solution:

RemoveIf(DateRange, DateValue(Text(Date,"[$-en-US]mm/dd/yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);

My Date in the SP-list seems like it's in a "german format", but I'm using Chrome always in englisch, so my Powerapps uses the US format for dates. So only a tiny change to the format worked very well!

Best regards