sharepoint date time issues

Question

We have a few calculated fields within a sharepoint 2010 list.

The calculations are: =DATE(YEAR(Start),MONTH(Start),1) which would return the start of the month.

and =DATE(YEAR(Start),MONTH(Start)+1,1)-1 which would return the end of the month.

If we have our Regional Settings in sharepoint 2010 for the user set to 'follow web settings', it works like a charm and returns 12/1/2011 and 12/31/2011.

However, if someone wants to personalize their calendars to auto adjust to their tmie zone, they can click My Settings and then My regional settings and change their time zone appropriately.

But, when th is happens, the date calculations above subtract a day from the results, and return 11/30/2011 for start, and 12/30/2011 for end.

Is there a way around this, other than forcing everyone to use the same time zone?

Answer 1

Can't you just check the time zone and adjust the calculation based on that swith in your code?

Answer 2

It looks as though the answer is to how the date and time is calculated on the SharePoint list. When using =DATE anything type of calculation, you are actually SETTING a date, thus by setting a date to 12/1/2011 you are actually setting it too 12/1/2011 12:00:00 AM. And, when the regional times adjust on this, the time forces them to go back to the previous day.

It is not an intentional thing. And something that can be overcome by adding some time to the actual result:

=DATE(YEAR(Start + .5),MONTH(Start + .5),1)         
=DATE(YEAR(Start - .5),MONTH(Start - .5)+1,1)-1

As we can see from the above calculations, the .5 is actually adding some hours to the initial calculations, which give us the proper settings if users want to set their time zones to something more custom.

This doesn't work for all time zones, however, given that international time zones would probably cross the time difference from the .5 above, and mark it a day in the future, or another day behind.

But, for us, the issue is resolved, and our users can once again, set PST, EST, CST, MST and such in their regional settings.