The Root of the problem
Vote here for Microsoft to reveal this field.
My educated guess on why "All Day" events are so different
All day events need to span the time and space continuum.
Regardless of what time zone a user is in, “All Day” events start at the beginning of their day (0:00:00 AM), and end when their day is over (23:59:00 PM).
This means that an all day event is anchored to the time zone that the user is in. If you have users in 6 time zones, an all day event will start at 0:00:00 AM for each of them, thereby having 6 different start and end times.
On the other hand, events that have a specific start and end time (I call them Scheduled Events) are anchored to a single time zone, usually the default time zone of the person scheduling the event. If you have users in 6 different time zones, the start & end times will be adjusted for their time zone, so the event actually occurs at one time.
In order to do this, there has to be some voodoo magic going on in the programming behind the scenes. The result is that the data for All Day events is a completely different entity than events that have specific start and end times. However, in SharePoint, the start and end time data for these 2 entities are stored in the same fields.
While my deep-dive has been focused on SharePoint as a data source, I am guessing there may be similar issues with Outlook data and calendar stuff stored in Teams. Or it may be that the data connectors for Outlook data is more advanced than SharePoint. I can’t answer that because I haven’t explored it at all.
Handling events with specific start & end times is easy
Formulas and functions for events that have specific start and end times (I call them Scheduled Events) are pretty straightforward. Dates and times are accurately revealed. The logic is not difficult to figure-out.
All Day events are like a moving target
All Day events are a moving target, there’s a number of things that influence them.
Notable influences include time zone settings for:
(The engine behind PowerApps)
Just imagine how complicated the voodoo magic must be to make an “All Day” event show as “All Day” in every one of these places, regardless of how different the zone settings are.
How the issue manifests itself
What you’ll observe when you first notice this issue:
An All Day event will span multiple days, with its start date/time on one day, and the end date/time on the next. The start date/time may appear on the accurate date, or it may appear on the day before. The start date/time may appear on the accurate date, or it may appear on the day after.
In a nutshell
You’ll have events with a specific start and end time that will appear on your Power App accurately, and in the same list you will have all day events that are all over the place.
Handling both Scheduled events and All Day events is WAY complicated
To add an additional layer of complexity on the issue, every SharePoint events calendar has both All Day events, and scheduled events. Depending on how deep your test data is, and how much you’re paying attention when developing the solution, this issue may not be immediately apparent.
As a result, all formulas and functions in PowerApps need layers (and layers, and layers) of If/Than, And/Or statements to make them work.
In a nutshell
To work with SharePoint events in PowerApps, you’ll need to:
- Identify if it is an all day event
- If it is, Convert it to UTC (Coordinated Universal Time) using the TimeZoneOffset functions
- Do your calculation/filter/sort, etc
- While at the same time, handling scheduled events in a different way (using standard date/time functions)
- Put them back together so they are revealed the way you need them to be–All day events and scheduled events combined.
This impacts everything–how you load, calculate, filter, sort, reveal, stylize, etc…
EV - AH - RE - THING !
Here’s an outline of 6 different scenarios of common event item data inputs.
The table shows:
- How these items were entered in the SharePoint event
- Formulas used in Power Apps
- What the results are in Power Apps
- Straightforward date/time formula
- Time Zone Offset formula
This should give you a good sense of the issue.
Good data entry helps
When doing these sorts of calculations you’ll begin to notice data entry that doesn’t accurately reflect the actual event. For example, in a company-wide important events calendar it is common for folks to tag a paperwork deadline as an All Day event–when in actuality, the deadline is 5:00 pm.
The reason this has an affect is because once you create formulas to tear the data apart so it can be used in Power Apps, it is challenging to put it back together and reveal it in the way that people are used to.
The final outcome is that an All Day event may end up being revealed with a start time of 00:00:00 AM and an end time of 23:59:00 PM instead of simply showing “All Day”. As you deal with this issue, you’ll see there are a number of other examples like this.
This is one area where Contributor training will have a lasting impact.