by As you know, PowerFx draws parallels from Microsoft Excel. In fact, Microsoft told us that they want full parity between Power Apps and Excel’s function in the past. But there’s one major point that Excel and Power Apps differ, and that’s the interpretation of the year 1900. I don’t want to make this a history lesson, but it’s interesting to understand that Excel considers 1900 a leap year. It’s a but one that they could never fix due to the market dominance of Excel and the sheer amount of files that exist with this date calculated with this in consideration.
Let’s check how this impacts the Power Platform, especially Power Apps.
The issue
As you may not, Excel stores the dates in a number format. The number represents the number of days elapsed since the 1st of January 1900. I go into a lot more details in my article “Power Automate: Convert Date to Excel Number,” but I want you to understand that if Microsoft fixe the issues:
- All dates will go back one day, and only this is a big deal. Think about how many dates are stored worldwide in every Excel file.
- Any related date function will provide you incorrect results.
So Microsoft had a huge decision to take. Keep the results incorrect and keep party between Excel and Power Apps or “fix” the issue. By keeping the issue, they would have many issues with other data sources that Power Apps users like the Dataverse or SQL Server, to name a few. So they did the right thing, in my opinion, and fixed the issue.
So that’s the impact for us?
The proof is in the pudding.
So let’s start by doing two things. First, let’s check how the data in Excel is generated and then in Power Apps. To be able to compare, let’s use the ISOWeekNum function. This function will provide us the week number of a date. We’re using this function for two reasons. First, to understand what the same function returns for the same date and see the behavior of the dates close to the start of the issue (29th February 1900).
Here’s the function in Excel:
If it fails for the 30th of February (that’s expected) and provides week 9 for the 29th of February, so it’s a valid date, proving that it’s a leap year. Now let’s look at Power Apps and see what we get.
Let’s unpack a bit of what we’re doing. To ensure the date we’re getting, we’re using the “Date function” that has the input of a numeric value for the year, month, and day. Next, we defined the TextInput to put a value and see what we get (you’ll see why in a minute). After that, we call the ISOWEEKNUM function and get the number of the week. Now let’s check the 29th of February 1900 that may exist or not depending on if it’s a leap year.
Now we see where things differ. When we generate the date with an invalid value, Power App will return the next available date (not an error). So, even if the function returns the same value, we see that the date starts to differ and that it’s not a leap year.
Final thoughts
The question until now is why I’m going into so much detail when it comes to a date?
Dates are important, and these details can bring you a lot of headaches if you don’t know how to deal with them.
The date is so far away that most of you won’t even have this issue, but knowing these “gotchas” is essential if you don’t want to spend a bunch of time debugging something that should be correct, but it isn’t.
