Strange goings on with the format of dates in SharePoint Calculated Columns

Strange goings on with the format of dates in SharePoint Calculated Columns

I came across a weird issue this morning whilst debugging a Web Part. I had a list that stored a date in one column and a Calculated Column that returned a date after applying a formula.

The regional settings of the site were set to English (Australia) – see screenshot below.

SharePoint Regional Settings

My calculated column was set up like so (formula and columns blurred out) -

SharePoint Calculated Column

Looking at the list everything appeared fine and the dates were displayed in the correct format (dd/MM/yyyy). Expiry is the calculated column.

SharePoint List

However when accessing the list items programmatically and trying to convert the Expiry date into a DateTime it was throwing an exception. The dates being returned programmatically were in the below format -

12/15/2011 10:00:00

This is clearly an invalid date when compared to the culture of my machine (UK).

What I ended up doing was converting the date using US culture (en-US) and then changing the format before converting back into a DateTime.

Below is the code snippet that I used to do this – has anyone seen this issue before? or does SharePoint always store the dates for calculated columns in the Content DB as US format?

[code lang="csharp"]

IFormatProvider provider = new CultureInfo("en-US");
DateTime date = Convert.ToDateTime(myItem["DateColumn"].ToString(), provider);
string convertedDate = date.ToString("dd/MM/yyyy");
DateTime date1 = Convert.ToDateTime(convertedDate);

[/code]

I would love to hear back from anyone who has seen this before or can shed any light on it.

One thought on “Strange goings on with the format of dates in SharePoint Calculated Columns

  1. Sue

    I am having the exact same problem. I have two dates in the same webpart. 30/11/1899 and the correct date if you click the document. I’m trying to set up email notifications and I believe the bad date is nullifying the correct date and making my notifications fail. I am actually googling hoping someone has a fix. I wish I’d found one here.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">