The Issue with PowerApps and SharePoint Calendars

The Work-Around

Contents

The Work-Around

  • Time Zone Offset function must be used for All Day events, but screws-up scheduled events

 

  • To do anything, you must first evaluate if the item is an All Day event, and then use separate calculations for those that are, and those that are not.
    1. Translate to Universal Time (UTC)
    2. Evaluate if the end time = 23:59:00, and the start time is 0:00:00
    3. If so, Calculate A, otherwise Calculate B

 

  • A= All Day events
    • Example calculation logic to show Start & End times for all Today’s events:
      • Convert to UTC
      • Evaluate if all-day event
      • If so:
        • Show Text “All Day” instead of Start time
        • Don’t show end time

 

  • B = Scheduled events
    • Example calculation logic to show Start & End times for all Today’s events:
      • Standard function = ThisItem.EventDate to reveal start time and ThisItem.EndDate to reveal end time

 

  • You usually need to do 2 things
    1. Making the data calculation/function
    2. revealin it in the right format.

 

  • Each of these 2 things has complex If/Than And/Or statements, so you end up with a minimum of 4 things you’re trying to accomplish just to perform what should be a simple calculation.
    • This makes it easy to get confused, that’s a lot to handle in one formula.  To keep everything straight, I generally tried to:
    • Do the data calculations for everything in the standard Data > Text property function area in PowerApps.
    • Use the Design > Color property function area to calculate the show/hide/highlight part of the equation.
It is what it is
Ad

Walking-through my formula build

#ACTIVITY / LOGICSTART DATE / TIME FORMULAEND DATE / TIME FORMULA
1Reveal Scheduled event Start Date & timeThisItem.EventDateThisItem.EndDate
2Add FormattingText(ThisItem.EventDate,"[$-en-US]mm/dd hh:mm AM/PM")Text(ThisItem.EndDate,"[$-en-US]mm/dd hh:mm AM/PM")
3Convert Start Date to UTC DateAdd(ThisItem.EventDate, TimeZoneOffset(), Minutes)DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes)
4Use Military TimeText(ThisItem.EventDate,DateTimeFormat.LongTime24)Text(ThisItem.EndDate,DateTimeFormat.LongTime24)
5Evaluate if the Start Time ends with "0:00:00" and end time ends with "23:59:00"EndsWith(Text(DateAdd(ThisItem.EventDate, TimeZoneOffset(), Minutes), DateTimeFormat.LongTime24), "0:00:00")EndsWith(Text(DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes), DateTimeFormat.LongTime24),"23:59:00")
6If line 5 is true, then format to look nicely (mm/dd time)If(EndsWith(Text(DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"23:59:00"),Text(DateAdd(ThisItem.EventDate, TimeZoneOffset(), Minutes),"[$-en-US]mm/dd hh:mm AM/PM"),Text(ThisItem.EventDate,"[$-en-US]mm/dd hh:mm AM/PM"))If(EndsWith(Text(DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"23:59:00"),Text(DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes),"[$-en-US]mm/dd hh:mm AM/PM"),Text(ThisItem.EndDate,"[$-en-US]mm/dd hh:mm AM/PM"))
7if line 5 is true, then format as "All Day" instead of showing timeIf(And(EndsWith(Text(DateAdd(ThisItem.EventDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"0:00:00"),EndsWith(Text(DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"23:59:00")),"All Day","Not all day")If(And(EndsWith(Text(DateAdd(ThisItem.EventDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"0:00:00"),EndsWith(Text(DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"23:59:00")),"All Day","Not all day")
8Add in an If/Than statement to accommodate scheduled events as showin in lines 1 & 2If(And(EndsWith(Text(DateAdd(ThisItem.EventDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"0:00:00"),EndsWith(Text(DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"23:59:00")),"All Day",Text(ThisItem.EventDate,"[$-en-US]h:mm AM/PM"))If(And(EndsWith(Text(DateAdd(ThisItem.EventDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"0:00:00"),EndsWith(Text(DateAdd(ThisItem.EndDate, TimeZoneOffset(), Minutes),DateTimeFormat.LongTime24),"23:59:00")),"All Day",Text(ThisItem.EndDate,"[$-en-US]h:mm AM/PM"))
9Turn text red or add a red frame on a box if the event is flagged as an important one. formatting like this is done with formulas in the "Color" property function area. If(ThisItem.Highlight_x0020_Indicator.TermGuid="a7db2b46-6056-451f-8552-75fda512502d",RGBA(254,38,53,1),RGBA(162,173,181,1)) same

 

How useful was this information?

Click on a star to rate it

We are sorry that this post was not useful for you!

Let us improve this post!