Q:  Why when I create a calculated column in a SharePoint 2007 or SharePoint 2010 list do users see the wrong date?

Q: Why when I create a calculated column in a SharePoint 2007 or SharePoint 2010 list do users see the wrong date?

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.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish