none
Calculated Date Column Returns 30 December 1899 RRS feed

  • Question

  •  

    Hi!

     

    This might be a bug in SharePoint... I wanted to have the Created column without the time, but since that doesn't seem possible I created a calculated column called Date Created which returns a Date only (ie- no time) and the formula is simply "=Created".

     

    This should work right? Well it does and it doesn't. After a while I noticed some of the values had become 30.12.1899. If I edit the calculated column under list settings without making any changes but just clicking OK, all the values are updatet and correct, but adding new items to the list sets the Date Created value back to 30.12.1899.

     

    Weird... I'm using Norwegian locale on this server so that might have something to do with it. The only thing I can think about is that the Date Created calculation is being done before the value of Created is set so that in effect this is a date null-value... Anybody else had a problem with this?

    Thursday, January 24, 2008 10:35 AM

Answers

  • I know this is kinda an old thread, but I was running into this issue and none of the above suggestions helped.

    putting brackets [] around the column i was using as a basis for my calculation fixed it.

    So, for example

    =TEXT([Created], "YYYY-mm"")

    Wednesday, May 23, 2012 1:18 PM

All replies

  • I've ran into this issue before, even SP1 did not fix it.

    Here is a work around: create a workflow in SP Designer, let this workflow to assign "Created" value to your column, make sure workflow starts on item creation

     

    Friday, January 25, 2008 10:10 PM
  • Hi,

     

    I have the same issue when using the modified date column in a calculated field. What i try to do is to create a field that shows only the modified date (e.g. not the time).

    When I try to create a calculated field with =modified, set the data type to date and time and check 'date only' format my calculated field returns 30/12/1899.

     

    When i type out the formula =DATE(YEAR(Modified),MONTH(Modified),DAY(Modified)) it returns 30/12/3799.

     

    Regards and thank you for the (any) response.

    Monday, February 11, 2008 8:48 AM
  • Try changing the data type of the column to "Single Line of text" and then use the following formula:

    =TEXT(Created;"dd.mm.YYYY HH:mm")

    This worked for me.

    Regards,
    Rene

    Wednesday, January 27, 2010 9:03 AM
  • I had the same issue when ever an item was edited.

    I had a similar formula to what Rene has except I had lower case "y" characters. When I made them uppercase, I could not reproduce the 1899 date. My formula looks like this.

          =TEXT(Created,"YYYY")

    and

          =TEXT(Created,"YYYY-mm")

    Hope this helps.

    Tuesday, July 26, 2011 7:37 PM
  • I know this is kinda an old thread, but I was running into this issue and none of the above suggestions helped.

    putting brackets [] around the column i was using as a basis for my calculation fixed it.

    So, for example

    =TEXT([Created], "YYYY-mm"")

    Wednesday, May 23, 2012 1:18 PM
  • Thanks Thanks Thanks !!!!

    I loose lot of time (and hair ...) with this f**** bug ...

    Friday, September 7, 2012 1:36 PM
  • That worked great for me. Thanks very much. I simply changed the field to return date and time and it corrects itself.
    Wednesday, December 4, 2013 12:25 PM
  • I put [] around the column and it's reverted back but it worked for me with YYYY :) 

    For example:

    =TEXT(Modified, "YYYY")

    Tuesday, March 31, 2015 2:01 AM
  • Thank you for this! Yeah, it's old and simple, but it saved me an hour or more diagnosing the problem while a customer was waiting for a fix.
    Wednesday, October 26, 2016 1:10 PM
  • Still helpful after 6 years - thanks!

    LVJC4EVR

    Thursday, November 1, 2018 7:30 PM