Power Platform Community Forum Thread Details

Data imported from Excel is different in Dataverse table

Good evening,

I'm pretty new to Dataverse and am in the process of migrating my data from Excel. I've created a table with the appropriate columns to match up, and formatted by data to facilitate easy transition (or at least I think I did, anyway 😲). When I import the data from my Excel file, I've noticed it appears to be different in the Dataverse table than it was in Excel. Most notably, the dates don't seem to be right.

Here's my source Excel file. The oldest date it contains is 1/1/2022:

When I look at my Dataverse table after importing from this file, I see this:

I see the date column contains entries for 12/31/2021. This is puzzling, since the source file doesn't have any dates prior to 1/1/22. Is this an issue I'm causing somehow? An issue with the source file, or some kind of formatting problem perhaps?

Thanks in advance for any insight you may be able to provide.

Categories:

Microsoft Dataverse

HI @Anonymous

This seems to be an issue with the date format and localization.

Some date fields are affected by timezones. For example, if you import a field as 2022/01/01 and it's stored as UTC time in the DB (with value 2022/01/01 00:00:00), but your personal timezone is for example US east coast you'll see it as 2022/12/31 because it'll take 5 hours from it.

If I'm not wrong there's a couple things you can try. You could change the type of behavior on the field to ignore timezone.

See here how the different behaviors work: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/behavior-format-date-time-field#date-and-time-column-behavior-and-format

Other option, if you need to keep timezone conversion, and this one I'm a bit less certain since it's been some time since I've used it, but if you match the import user settings to yours it should keep the same format.

You could also include the time component and add the extra hours to match UTC (which is what's stored on the DB)

Hope this helps

Quick Links

Subscribe to this forum!