I'm using oDataFeed to retrieve sharepoint list data and place it in excel sheet. One of the fields in the list is date time field. I have noticed that the date is correct in the list but not on the excel sheet. For example, list shows 2/25/2016 12:00 AM and excel shows 2/24/2016 9:00 PM.

What's the cause of this and how do I fix it?

  • What time zone are you in? – Dylan Cristy Feb 29 '16 at 17:13
  • @DylanCristy GMT +3. I have noticed this pattern too, it adds 3 hours to the actual date. – Khalifa Sabt Feb 29 '16 at 17:14
  • OData has Edm.Date and Edm.DateTimeOffset primitive types. I think you need the second one – Aziz Kabyshev Feb 29 '16 at 17:44

SharePoint stores DateTime values internally as UTC, and corrects it to the local time when displaying through the UI.

If you are in GMT+3, and the values you see from the data feed are consistently three hours behind what you think they should be, it's likely that you are getting the raw UTC values form the data feed, and need to correct for your local offset.

  • yes, that was it. I have created an additional column that adds +3 hours to the date to get the correct values. thanks!! – Khalifa Sabt Feb 29 '16 at 17:42
  • This is the correct answer, and this website has the full translation guide for anyone who needs to translate UTC to their time: earthsky.org/astronomy-essentials/universal-time – Toby Pior Nov 17 '18 at 0:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.