Unanswered

DateTime format comparison issue in RemoveIf depending on user language settings

(0) Share

Report

Posted on by ToRo

Hey experts,

I'm new to PowerApps and I have a strange format problem with my DateTime in the RemoveIf function. The problem depends on the language settings of the user and I don't know how to fix this.

I'm currently developing a Leave Request App, where employees can select a date range for their request.

From that date range (range stored in DateRange collection) the weekends and also holidays (stored in a SP-list named HolidaysGermany) should be removed.

The DateRange gets 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));

I had several tries for the removal at the beginning that didn't work:

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

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

And the removal finally worked with this:

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

This works like a charm, as long as the browser language or MS Teams is set to english!

Working Example

SP-List "HolidaysGermany":

Selected DateRange:

Will give me the expected result of 2 days, since 4/9/2020 and 4/14/2020 are business days in germany:

Chrome is always set to use english as default language on my PC, so if I use Firefox, the holidays will not be removed.

NOT working example

Same DateRange:

Different result:

I'm desperate with this problem. We have international colleagues as well, so the best thing would be if the app does this comparison in the RemoveIf correctly, regardless of the language settings.

Is there a way to set the language in the app regardless of the language settings of the PC or browser of the employee?

Any help is appreciated!

Thanks and best regards

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Sort by

v-xiaochen-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @ToRo ,

According to your description, let me explain why you encountered this problem.
Firstly, let's take this formula as an example:
RemoveIf(DateRange, DateValue(Text(Date,"[$-de-DE]dd.mm.yyyy")) in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);
This formula uses text() function and datevalue() function. Text() function uses “[$-de-DE] dd.mm.yyyy”. It changes the format to Germany.
But you did not set the "language" parameter of the datevalue() function. It converts through the language set by the user.
So the reason why the formula is invalid is that the two format conversions cause the month and day to be reversed.
The collection could not find the record with the reverse date, so it failed to delete the record correctly.

Secondly, You can try my two methods.

1\ Do not convert the data type.
RemoveIf(DateRange,Date in HolidaysGermany.DateOfHoliday)

2\ Add corresponding parameters in the datevalue function. Take the above formula as an example:
RemoveIf(DateRange,DateValue( Text(Date,"[$-de-DE]dd.mm.yyyy"),"de-DE") in HolidaysGermany.DateOfHoliday)

Best Regards,
Wearsky

Was this reply helpful? Yes No
ToRo 13 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey @v-xiaochen-msft,

thanks for your reply. Unfortunately both suggestions do not work. I tried both versions in Teams and also in different browsers. The first one
RemoveIf(DateRange,Date in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);
doesn't work at all. It's only removing weekends.

The second one
RemoveIf(DateRange,DateValue( Text(Date,"[$-de-DE]dd.mm.yyyy"),"de-DE") in HolidaysGermany.DateOfHoliday || Weekday(Date) = 1 || Weekday(Date) = 7);
works only in my Chrome browser with english as default language setting as before.

I also tried to perform the removal differently with this:
ForAll( DateRange, If( CountIf(HolidaysGermany, DateOfHoliday >= ThisRecord.Date, DateOfHoliday <= ThisRecord.Date) > 0 || Weekday(ThisRecord.Date) = 1 || Weekday(ThisRecord.Date) = 7, Remove(DateRange, ThisRecord); );
but I don't get it to work, since I get the error that I can't use Remove in the same data source used in the ForAll.

Maybe you have another solution for that. Any help is still appreciated!

Thanks and best regards

Was this reply helpful? Yes No