excel dates are lagging by one day when connected to dataverse

[chahineatallah1]

Hello

I have some dates in my dataverse tables, these dates were generated by power app

When i connect this dataverse table in excel, dates are lagging by one (starting in september when i started using dataverse before i was using excel table), please note that my date column in dataverse is timezone independant

I think this is something that has to do with UTC time zone of dataverse and the date when i entered the date into my app vs excel

Any suggestion on how to fix it?

dataverse tableexcel tables

[FLMike]

Morning,

Sorry you have this issue. I am not sure what you mean by the following:

When i connect this dataverse table in excel

Do you mean when you insert the values from Excel into the database.

So now you write directly to the database from the power app instead of into excel?

[ChrisPiasecki · Accepted solution]

Hi @chahineatallah1,

A couple clarifying questions:

• What timezone are you in?  
• Are all users in the same timezone? 
• Were you using a Canvas App or Model Driven App (or perhaps a Flow) to populate the dates?
• Was the date column originally created with User Local behavior and then changed to Timezone Independent after data was created? 

I recommend reviewing the Date and Time behavior of Dataverse . If you changed your column behavior from user local after you created you created your data, then you will see incorrect offset in the UI because existing data is not automatically converted, it will still be stored with its original offset, but when retrieving it, it will not be converted. 

For these reasons for Date fields I almost always choose a Date behavior of Date Only, especially if all my users are in the same time zone. 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[chahineatallah1]

Hi Chris, thanks for the reply

I was using canvas app which takes my date and patches it to dataverse

My timezone is abudhabi i think GMT+3

The date was user local and it was showing wrong date in excel (lagged by one day mainly for september)

So today i changed the date column to time zone indepenedent (logically time zone independent means that date should stay same regardless of timezone)

do you have any suggestion on how to fix it? excel and dataverse to have exactly same date

[chahineatallah1]

Hi Mike

In excel you can connect to different datasources, if you go to Data tab

So one of datasources is dataverse

so when i connect to my dataverse , date that appears in excel is not same as in dataverse

[ChrisPiasecki]

Hi @chahineatallah1,

Ah yes - - so as I mentioned above, if you change date behavior, it will only affect the column values that were created or modified after the change. Your existing records will still contain the current datetime offset in the database. 

You will need to convert the time of those existing records by adding the number of hours equal to the offset of the local timezone it was created using.

If you retrieve the records using the Web API, you can see the actual value in the database which will contain the UTC time and offset, and formatted value which will just show the date only portion which you'll see in the user interface. Any record that doesn't show 00:00:00 for the hours:min:seconds will likely be all your affected records. You can query the table via the API via the table hub in the maker portal.

 

If it's just a small number of records, just edit all of the affected records in Excel or via the maker portal by saving the column with the same value (you might need to change it to a diff date first, save, then update it back the right date again and save). The record will be saved in the database with 00:00:00 as the time portion.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[chahineatallah1]

Hi Chris

For this one "You will need to convert the time of those existing records by adding the number of hours equal to the offset of the local timezone it was created using"

where to do that? you mean in dataverse table ?

[ChrisPiasecki]

Hi @chahineatallah1,

Yes the Dataverse data. You can update the Dataverse records via Excel manually if you're connected to the Dataverse table using the Power Apps add-in. Or you can manually set the correct date one record at a time in the maker portal under Tables.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.