Sharepoint date calculation bug...

Question

I think I have found a bug in Sharepoint regarding date fields and calculations. Here is how to replicate:

1. Make a sharepoint event/calendar list (where items have a starttime and end time)
2. Make sure your Sharepoint site is on a different timezone than GMT.
3. Make some entries in the list (some calender events). Make one of the entries a "full day event" where you do not specify the start time. The system then sets the starttime clock to 00:00 which is correct.

So far ... so good!

4. Now add a column to the list, lets call it "CalculatedStart", make it of type calculated field and of format "Date & Time". Then type in a simple formula like "=StartTime" (or whatever the name of start-time column is in your system language locale).
5. Now observe that for entries that are full day events (where clock is 00:00), the calculation skews the time by several hours (7 hours for me living in Oslo, Norway).

This IS a BUG, quite serious one too!

Good luck in hunting it down and rewarding people who have spent time being frustrated over it :)!

Best regards,

Eirik Y. Øra, Oslo, Norway

Answer 1

Hi Eirik,

Thanks for reporting this issue. While I following your steps to do the test. Here is the list with a start time and end time:

The Start and End are typed date. 

The Calculation field is calculated data which equals Start. 

Could you share more about the event/calendar list and the column add to the list?

I also tested event in the calendar. I open the group calendar and add a meeting set to a 12:00 PM(My timezone was changed to UTC+0), then the calendar shows the meeting starts 4:00 AM, which is correct for the timezone of the site(UTC-8). SharePoint shows the time according to the site timezone, which site admin can change it in Site Settings > Regional Settings. 

Regards,

Alex

Answer 2

Hi Eirik,

I just test to change the timezone of the SharePoint site, the calendar updates the time itself. We suggest you refreshing the page first.

Regards,

Alex

Answer 3

Hi Eirik,

Thanks for the reply and your suggestion about setting the timezone. We suggest you post the suggestion in SharePoint UserVoice, that's the best way to tell the team how to improve the product. I fond this one is talking about timezone too, you may consider voting for it: Automatic site locale, timezone.

Sorry for all the trouble and your understanding is very appreciated.

Regards,

Alex

Answer 4

Hi Alex,

You were right. It turned out to be a site timezone setting issue. It had been set to US timezone while I was operating out of Norway.

I have corrected the issue now and it all works as expected. However on the downside, every event in that big calendar we have has now shifted 8 hours and has to be fixed manually!

This is a big usability design flaw, MSFT. When an admin sets up a new website on Sharepoint, the software should put up a big red triangle saying "Admin sits in Norway timezone. Site is in SF timezone. Are you sure?". There are so many settings in Sharepoint, but this one is quite essential and can lead to big issues.

Thanks,

Eirik