cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ToRo
Frequent Visitor

Can't get RemoveIf to work with multiple conditions

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):

ToRo_1-1606400924461.png

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):

ToRo_0-1606400871535.png

Do you have any suggestions? Any help is appreciated!

 

Thanks and best regards

1 ACCEPTED SOLUTION

Accepted Solutions
ToRo
Frequent Visitor

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

View solution in original post

3 REPLIES 3
gabibalaban
Dual Super User
Dual Super User

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 !

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
Frequent Visitor

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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (5,346)