Q: When I create a calculated column in a SharePoint 2007 or SharePoint 2010 list to convert a date from another column to the first day or last day of the month, some users in other time zones see the wrong date--why is that?
A: You probably created a calculated column with something like the following:
First day of month: =DATE(YEAR(somecolumn),MONTH(somecolumn),1) Last day of month: =DATE(YEAR(somecolumn),MONTH(somecolumn)+1,1)-1
When you enter a date, you are actually setting a date and a time. So if you set the date with your calculation to 12/1/2011, you have actually created a date and time of 12/1/2011 12:00 AM.
When your users set their preferences to a time zone one hour before or after your time zone, they will then see 12/1/2011 1:00 AM or 11/30/2011 11:00 PM.
One solution is to fudge the date a bit by adding a few hours +/- to your calculation. Try the following (where .5 = 12 hours):
=DATE(YEAR(somecolumn + .5),MONTH(somecolumn + .5),1) =DATE(YEAR(somecolumn - .5),MONTH(somecolumn - .5)+1,1)-1
If you are international and need to support all time zones, then the above will not work for the time zones +12 or -12 hours from your time zone.