One of the technical challenges we have in the UK is that for half of the year we are in the UTC time zone that we’re all familiar with, and the other half we’re in British Summer Time (BST). Those lucky few that keep the same time zone all year don’t know how easy they have it!

It can be quite confusing, as some digital solutions (including Dynamics 365) host UTC and our local time as separate time zones but call both UTC, but others don’t always make this distinction, and you may have seen data that you just submitted appear with a date stamp of ‘1 hour ago’. This is easily done if you’re non-technical. Why would you ever consider having to change your time zone if you can already see ‘UTC’ in the dropdown?

This doesn’t have a major material impact until you’re working with date values without times, particularly if the solution you’re using only allows you to control the date entry from the front end, and not the time entry. The difficulty we face in this scenario is that an application could even show yesterday’s date!

Yesterday’s date? Are you sure?

Well submitting data at 2pm during your workday doesn’t cause too much of an issue, you might see data entry from 1pm instead. But what if you submit a ‘date only’ value, or, (hopefully you’re not working at this time) but at some time between 00:00 and 00:59?! In this instance, the application can often confuse the user and present the data back as yesterday’s date instead!

How do I prevent this?

Fortunately we don’t have any problems submitting data as these will always be submitted in UTC and convert appropriately.

The issue we face occurs when we are trying to retrieve data from a data source, where (for example) the database stores the date as 30/07/22 00:00:00, but our Canvas App reads this from the data source as 29/07/22 23:00:00 due to the database storing our submitted date in UTC.

I discovered this when using the Outlook Tasks Connector to pull in today’s To Do items into a Collection, rather than using the Today() function to compare dates.

Check out the example below:

DateAdd(DateTimeValue(DueDateTime.DateTime),-TimeZoneOffset(),Minutes)) = Today()

“Add the negative of my local timezone offset in minutes to the local date, and then show me all of the To Do Items where the DueDateTime.DateTime value is equal to the newly calculated date.”

Note: For this particular connector I needed to explicitly specify DateTimeValue as the format, but you don’t need to do this for all Connectors.

That’s all. Fortunately Power Fx allows us to grab the time zone offset for the time zone I am currently in, but we must be aware that this value is a negative, and therefore we need to negate the negative in order to add the correct number of minutes. I’ll be using this in every Canvas App I build now, particularly as I work in an organisation that spans multiple time zones!