0

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

Improve this question

1 Answer 1

Reset to default
0

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

Improve this answer
3
  • 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).
    – Eirik Y. Øra
    Commented Sep 9, 2018 at 20:02
  • Eirik, Which did you change? Server, Web App, or Site?
    – Mike Smith - MCT
    Commented Sep 9, 2018 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.
    – Eirik Y. Øra
    Commented Jan 30, 2020 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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