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?
Solved! Go to 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!
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:
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.
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
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!
@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.
User | Count |
---|---|
179 | |
120 | |
87 | |
44 | |
41 |
User | Count |
---|---|
245 | |
156 | |
128 | |
77 | |
73 |