Date Selection - No Weekends or Holidays

[tomgardzinski]

@CarlosFigueira

I have seen the other posts that seemed to fix this problem, and I incorporated your solution from this post, but now I've realized a problem.

If you can recall, I have an app where the user selects an "Exit Date". This exit date calculates three additional dates, that must not occur on a weekend or holiday. I've created a holiday table in onedrive.

The Exit Date determines the 3 month, 6 month, and 12 month days. All of which, as expressed above, can't occur on a weekend or holiday.

I'm currently using the below formula which you graciously supplied, specifically this is for the three month date:

If( Weekday(DateAdd(DataCardValueExitDate.SelectedDate, 90, Days), StartOfWeek.Saturday) > 2,  DateAdd(DataCardValueExitDate.SelectedDate, 90, Days), DateAdd( DataCardValueExitDate.SelectedDate, 90 + 3 - Weekday(DateAdd(DataCardValueExitDate.SelectedDate, 90, Days), StartOfWeek.Saturday), Days)) + CountIf(Holidays, Date >= DataCardValueExitDate, Date <= DataCardValue3MonthDate).

The problem is, this logic can still produce a value on a weekend. For example if the Exit Date is November 28, and accounting for 3 holidays (Dec 25, Dec 26, Jan 1), it returns a 3 month date value of Saturday, March 2, 2019.

I think I need a way for PowerApps to distinguish if in fact the 3 month date lands on the stat holiday but I'm unsure as to how to write the logic. Basically I need:

(If 3month=stat holiday, then bump to a non stat, and weekday)

Any thoughts?

[CarlosFigueira]

That is an interesting question, and by researching it I believe I've found a way to make the original expression simpler. Here's the new expression that you can use:

First(
    Filter(
    AddColumns(
        [0,1,2,3,4,5,6,7,8,9,10],
        "FutureDate",
        DateAdd(DataCardValueExitDate.SelectedDate, 90 + Value, Days)),
    Weekday(FutureDate, StartOfWeek.Saturday) > 2,
    IsBlank(LookUp(Holidays, Date = FutureDate)))).FutureDate

The idea is to use the range (0-10) to create a list of dates starting 90 days from today (or 30, or 180, or 365, depending on your requirement), and filter it for two conditions:

• It doesn't fall on a weekend (using the Weekday function as we had before)
• It doesn't fall on a holiday (checking if a LookUp in the holidays table Is Blank)

After the filtering is done, we can take the first item, and that will be the first business day that matches the conditions. Notice that you probably don't need to have a range from 0-10, but there are some countries that have week-long holidays, so that's the worst case I could think of.

The attached app shows this expression with a sample list of holidays.

[tomgardzinski]

Unfortunately, a test on my side shows that a holiday can still be selected. See attached. Maybe it is because of date formatting comparison between excel onedrive and PowerApps? In previous tests, I created a label in my powerapps to see what the 3-month values were and they are being interpretted as, using the example below, 01/01/2019. In excel onedrive the value shows as 01/01/2019. Therefor the two values appear to be visually equal to each other, but technically, are they different? Something I'm missing or not seeing?

[CarlosFigueira]

This is a known issue with how dates in Excel and PowerApps interact. Basically, the dates that are received from Excel are interpreted in PowerApps as if they were from the Universal Coordinated Time (UTC), and date/time values in PowerApps always work with the local time, so you'll have a time zone offset that will make the value from Excel different than what you expect. The post at this link talks in detail about this problem (it's about SQL Server, but the same applies to Excel).

There are a couple of options for this; one is to have the dates in the Excel table as strings, which you can achieve by preceding the date values with a ' character:

And in your app you can convert the date to a string to compare with it:

First(
    Filter(
    AddColumns(
        [0,1,2,3,4,5,6,7,8,9,10],
        "FutureDate",
        DateAdd(DataCardValueExitDate.SelectedDate, 90 + Value, Days)),
    Weekday(FutureDate, StartOfWeek.Saturday) > 2,
    IsBlank(LookUp(Holidays, Date = Text(FutureDate, "yyyy-mm-dd"))))).FutureDate

Another alternative is to use the same workaround as listed in the blog post, which would remove the time zone offset and make the value back to what it was displayed in Excel:

First(
    Filter(
    AddColumns(
        [0,1,2,3,4,5,6,7,8,9,10],
        "FutureDate",
        DateAdd(DataCardValueExitDate.SelectedDate, 90 + Value, Days)),
    Weekday(FutureDate, StartOfWeek.Saturday) > 2,
    IsBlank(LookUp(Holidays, (Date + TimeZoneOffset(Date) / (24 * 60)) = FutureDate)))).FutureDate

[tomgardzinski]

Option 1 above didn't work as I received an incompatible value yellow warning. "Incompatible type. We can't evaluate your formula because the values being compared in the formula aren't the same type. The left value is a DateTime type and right value is a Text type" Option 2, for me at least, was very technical and I couldn't comprehend it. (The beauty of most Powerapps and Flow is that no technical computer programming background is required). So I abandoned that option after receiving some 'cascading' errors as it trickled into other date pickers that I have in my app.

[tomgardzinski]

So, I've gone back to coding as such: First( Filter( AddColumns( [0,1,2], "FutureDate", DateAdd(DataCardValueExitDate.SelectedDate, 90 + Value, Days)), Weekday(FutureDate, StartOfWeek.Saturday) > 2)).FutureDate Then prompt the user via a popup to ensure dates are not a holiday. Currently my excel column in Holidays.Date is set to "Date" and typed in as 1/1/2019, and 12/25/2018 for two digit months and days. Thoughts?

[CarlosFigueira · Accepted solution]

As I mentioned before, Excel dates and PowerApps really don't play together well, unfortunately (I'd suggest creating a new feature request in the PowerApps Ideas board to make this integration better). I don't know how much control you have over your Excel table, but one option would be to split the date into three parts (day, month, year), and with that those problems would be avoided:

We would then need to update the filter expression to use to "reconstruct" the date in the app:

First(
    Filter(
    AddColumns(
        [0,1,2,3,4,5,6,7,8,9,10],
        "FutureDate",
        DateAdd(DataCardValueExitDate.SelectedDate, 90 + Value, Days)),
    Weekday(FutureDate, StartOfWeek.Saturday) > 2,
    IsBlank(LookUp(Holidays, Date(Value(Year), Value(Month), Value(Day)) = FutureDate)))).FutureDate,

Notice that sometimes numeric values in Excel are imported as text columns, so I had to use the Value function to convert them to numbers prior to recreating the date using the Date function.

Hope this helps!

[tomgardzinski]

@CarlosFigueira!

I think you solved it! Except for the errand extra comma at the end of your expression, it seems to be working. I'm going to run it to do some additional testing to make sure, but I think it is a winner.