Why doesn't SharePoint understand British Summer Time?

I'VE HAD THIS PROBLEM A COUPLE OF TIMES ... you add some fiddly code to your Flow (or Workflow) to generate a handy Calendar Invite (.ics file) for your end user, but the resulting calendar entry is an hour out. For some reason, Microsoft have never been able to offer us anything other than UTC time in SharePoint and Outlook. And it's an awful pain for us developers.

I was creating a Meeting Calendar for an event our company would be attending. My colleagues would need to book the meeting space set aside for our company to use at the event, and it made sense for us to offer an automated booking service for those attending.

I could have used a SharePoint Calendar as the starting point, but I wanted to have something a bit more user-friendly, so I opted for a SharePoint Custom List. This would also give me a bit more control over the fields in the list.

So I set up the list and modified the input form in PowerApps to make it a bit more visually attractive (exactly how that's done can be discovered with a quick Google search - it's outside the scope of this article) and I ended up with something like this:

To keep things simple, I'm asking the user to just select a Date, Time and Duration of the meeting they want to book. (They're also asked to select a Venue, but that's not important right now.)

You can see that all the attendee has to do is add the Name of the Host (Name field) - the person filling in the form may not be the meeting host - add the names of any colleagues who may also be attending, then select the Date of the meeting, the Time of the meeting and the Duration of the meeting. I was planning to have the Power Automate Flow do the configuring of the EventDate and EndDate for the meeting.

I'm deliberately mimicking the column names that are found in Calendars, as these are needed when it comes to generating the .ics file later in the process. It doesn't hurt to have a Location column as well.

These three columns are hidden (and not included in the NewForm) because the user never needs to see them.

ADDING EXTRA COLUMNS TO THE LIST

So, the Venue, Date, Time and Duration fields are simple Choice columns in the SharePoint list, which return text values. If I was going to generate an iCalendar file, then I'd need to convert that text into valid Dates and Times. It's probably possible to do that in PowerApps (and I will have a think about that for next time), but because I wanted to get it done, I chose the Power Automate route.

I created an "On create" Flow and added the Site and List. Then I added the Update Item and Send an Email components. We'll fill those in later. But mainly, I needed to figure out a way to transform the simple text values in the List into properly formatted DateTime values. Then I discovered the Compose action.

It's funny how you can use a platform for years, yet still discover new things about it. I'd never come across Compose before, but what a great tool ...

The Compose action is an absolute wonder ... I'm so annoyed that I didn't know about it before ...

I added a couple of Compose actions then set about adding the scripting that would do the conversion. The trickiest was the Date. The scripting was fairly straightforward, but I did get some unexpected results, at first. So, use formatDateTime and transform the text value into a date by typing this script:

formatDateTime(triggerOutputs()?['body/Date/Value'],'yyyy-MM-dd')

... into the Expression area of the Compose function's Dynamic Content, here:

However, when I tested the output of Compose - Date action, the result was a scrambled date which had reversed the Day and Month values, so instead of 11th of May, it was rendering as 5th December. Straightaway, I checked the Region Setting of the parent site, but that was set to "English (UK)" and to the correct London TimeZone.

So why it was rendering the date incorrectly, I have no idea. And rather than spending ages trying to figure it out, I cheated and changed the formula to:

formatDateTime(triggerOutputs()?['body/Date/Value'],'yyyy-dd-MM')

... which did the trick!

Once I had the Date rendered correctly, I had to append the chosen time to it. This is where the second Compose action comes in. Using the concatenate command, I built this expressions and added it to the Expression field of the "Compose_-_Full_DateTime" action:

concat(outputs('Compose_-_Date'),'T',triggerOutputs()?['body/Time/Value'],':00Z')

All I've done here is build a DateTime value by structuring it in a format that SharePoint understands, eg:

2022-05-11T10:00:00Z

The real puzzler came when I tested the Flow to see whether the above would help me generate a calendar invite for the end user.

SO ... WHY DOESN'T SHAREPOINT UNDERSTAND BST?

There is annoying glitch in SharePoint where the TimeZone of a site can only be set to a UTC value. In the case of the "00:00 Dublin, Edinburgh, Lisbon, London" setting, that translates to Greenwich Meantime ... which isn't a problem in the winter, but becomes an issue in the Summer when UK clocks are wound forward by an hour. (I think in the US, this is referred to as Daylight Saving Time).

The result is that when you set a DateTime value in SharePoint and copy it over to Outlook, the date goes in at an hour ahead of the time you wanted.

There doesn't seem to be a fix for this and, though it comes up in many SharePoint and Microsoft forums, no one at Microsoft seems to have the appetite to fix it.

The upshot of this is, I needed to find a way to compensate for this anomaly myself.

Luckily, I found a function in my list of Flow actions called Subtract from time. I added the action, changed its name to "Subtract from Time - Because of BST" to remind me of why it's there when I come back a few weeks from now, then just set it to the following:

The Base time is set to the output from the previous action Compose - Full DateTime. The Interval is set to 1 and the Time unit is Hour. That's it.

Now we have all the components in place to configure the Update item action.

So open up the Update item panel and set it up like this:

• Set the Title to: "Meeting at [Venue value]".
• Set the EventDate field to the output from the Subtract from time - Because of BST action.
• Set the Location field to [Venue Value].

My last job was to set up the notification that goes to the Requestor once they click Save on the SharePoint input form. The main thing I wanted to do was to include an iCalendar invite so the Requestor could add the event to their Outlook calendar.

It's not hard to find on the Interweb, but to save you looking it up, here's the script for generating an iCalendar event:

[YourSite]/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=[YourListID]&CacheControl=1&ID=[ID]&Using=event.ics

For this to work properly, you must have at least the two Date columns in your list named EventDate and EndDate. Without these, the script just won't work. 

As mentioned earlier, you would do well to have a Location field as well, and to configure the content of the Title field in a way that makes sense (The Title field content will become the Name of the event when it's added to the user's Outlook calendar.)

The List ID can be found by going to the List's Settings window and grabbing it from the end of the URL window ...

Click on the image to enlarge it.

... and you'll need to replace [ID] with the ID of the list item we're processing at that point. I actually found it a lot easier to configure this formula using a Flow Variable.

So, add an Initialise variable action and a Set variable action, like this:

Then in the Initialise variable action, name your variable as "calendarInvite", set the Type to "String" and leave the Value field blank.

Next, in the Set Variable action, set the variable Value to:

<a href="[YourSite]/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=[YourListID]&CacheControl=1&ID=[ID]&Using=event.ics"><strong>Add the Event to your Calendar</strong></a>

You should be able to replace the "[ID]" with the item's ID from the When an item is created action ... so the Set Variable action looks like this:

But if that isn't working for you, try replacing the [ID] with the expression:

@{triggerOutputs()?['body/ID']}

All that remains is to compile the email notification that's sent to Requestor to confirm their booking. Because the variable now contains the text and the URL for the Calendar Invite, all you need to do is add the Variable to the body text of the email in the appropriate place, like this:

And that is pretty much it.

When the recipient of the email clicks on the email's Calendar link, the new event has a subject line of "Meeting at [Venue]", a seasonally adjusted EventDate and End Date, and even a Location value.

There may be a more "correct" way to deal with the fact that SharePoint is unable to adjust the time of the event for local, seasonal timeshifts, but at least this quick fix works.

If you wanted to be really ambitious, you could add a Condition action to the process to create two branches ... one for events in the winter (not British Summer Time) and one for summer when there is some form of daylight saving time in place.

Hope this helps someone ...