Microsoft Power Platform Date and Time Series

Importing dates with time from Excel through Power Automate

Introduction:

This Article covers the following scenario:

• Dates for appointments or bookings need to be imported from an Excel Spreadsheet. Time sheets are entered into a spreadsheet and then the spreadsheet is uploaded to be processed by a Power Automate flow.
• The Date and Time are in separate Excel columns. The Date is one column, Start Time is another column and End Time is in a third column.
• The Dataverse field type is Date and Time.
• The Dataverse field format is Date and Time.
• The Dataverse field Time zone adjustment is User local.

Note: The reason the Time zone adjustment is ‘User local’ is because Dynamics users from different time zones need to know when an appointment or booking will occur in their own time zone.

Nomenclature

This article will frequently refer to ‘UTC’. UTC stands for Coordinated Universal Time, and it’s the global time standard used to regulate clocks and time around the world. The Time Zone Offset is always 0 hours (UTC+0).

GMT stands for Greenwich Mean Time. It is a time zone that is based on the mean solar time at the Royal Observatory in Greenwich, London.

UTC and GMT are very similar, but they are not exactly the same. In this article, we will only use UTC.

Key points:

In this scenario, the Dataverse field Time zone adjustment is User local. Time-Zone Independent scenarios are addressed in a different article.

In the backend (SQL layer), all Dataverse DateTime values except “Time-Zone Independent” are stored as UTC. For Time-Zone Independent fields, dates are stored exactly as entered, without any time zone adjustment.

Power Automate or API access will retrieve Dates in UTC.

Excel stores dates as serial numbers. The numbers represent the number of days since a fixed starting point. For Microsoft Windows, this starting point is 1900-01-01.

Excel stores time as a decimal value, A full day is represented by the number 1 and the fraction of the day is represented by decimal numbers. For example, 12:00 PM (noon) is stored as 0.5 because it’s half of a 24-hour day. 12:30 PM = 0.520833333 (approximately)

Method

If a User Local field is set to 2025-04-30 14:00 by a user in Eastern Time (UTC-4):

1. It is stored as 2025-04-30T18:00:00Z (14 + 4 = 18) in the database.
2. When retrieved in Power Automate, it comes in UTC so you will see 2025-04-30T18:00:00Z (how it was stored. No adjustment is applied). In Power Automate, depending on the application and the location of the user, the Date/Time may have to be converted back to the appropriate Time Zone.

When retrieved in Dynamics 365 or a Power App, the date will be converted to the local time zone of the currently logged in User. So, the date will be seen as 2025-04-30 14:00 by a user in Eastern Time (UTC-4) and as 2025-04-30 12:00 by a user in Central Standard Time (CST) (UTC-6).

Preparation:

The Excel column that contains the Date needs to be formatted as Date.

The Excel columns that contain the Time need to be formatted as Time.

The Excel sheet that contains the data to be imported needs to be formatted as a Table.

Application:

Because we are using Power Automate, if any users are operating outside the UTC Time Zone, all dates will need to be converted to UTC before updating a Date and Time field. This also applies when searching for existing records by the Date field. Converting a date from one time zone to another is achieved by using the ‘convertTimeZone’ expression.

An Example of this, if a user operates from the East Coast of Australia is:

To get the local date from a date retrieved from Dataverse:

convertTimeZone(Datevariable, ‘UTC’, ‘AUS Eastern Standard Time’)

To set a local date to UTC so you can write to a Date field or perform a search:

convertTimeZone(Datevariable, ‘AUS Eastern Standard Time’,’UTC’)

In the scenario used for this article, if all users operate in the same Time Zone – Queensland, ‘AUS Eastern Standard Time’ can be set as the String value during the variable’s initialization.

If users operate in different Time Zones, you will have to create a Custom field in the User table (systemuser) and retrieve the correct Time Zone for each user that runs the Power Automate flow.

The Time Zone Name MUST be valid. Some valid Zones are listed below.

Conclusion

Managing Dates and Times in any database system can be complex for organisations that have users or stakeholders in different time zones. Fortunately, Dynamics 365 and Dataverse provide a number of different settings to accommodate most scenarios and use cases.

The key is to identify what a date/time field will be used  (for both now and in the future), understand implications of the format and advanced field settings, and plan accordingly.