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!

  1. 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).

  2. 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!

Best regards, Eirik


Is it a bug, or a feature? ;-)

Consider if you have two users, one has set their regional settings to time zone "A" and the other has set it to "B". Which time zone should be used for the start time of an "All day" event when the each user creates an "All day" event?

The root of the problem is that SharePoint supports setting the Time Zone at several levels.

  • Server / Farm (default for all of SharePoint)
  • Web application (default for all site collections and sites in the web app)
  • Site (default for all of the date/time columns in lists/libraries)
  • each user

Which one should be used to represent the start of a "All day" event?

Which one should be used in alerts?

With your problem, which should be used for Calculated Columns?

As dates are stored as numbers, you could just add/subtract the time difference in your Calculated column.

=StartTime - 1/24 * 7

SharePoint search has a similar time zone problem... http://techtrainingnotes.blogspot.com/2015/02/sharepoint-search-and-dates-sharepoint.html

  • You were right, Mike. We had an incorrect timezone setting on our Sharepoint installation. Corrected it and woops everything got shifted on all calendars by 7 hours. The real weakness is Sharepoint not alerting the admin that he has a timezone that does not seem to match his IP address timezone (or something like that). Sep 9 '18 at 20:02
  • Eirik, Which did you change? Server, Web App, or Site? Sep 9 '18 at 21:00
  • Sorry for late anwer. I changed inside the Sharepoint site admin pages in the Web App. So I guess, it is the site settings I modified. Jan 30 '20 at 12:30

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.