locked
Inaccurate result in [Modified] column, Calculating date based on [Modified] column RRS feed

  • Question

  • Hi,

    We are working on a SharePoint site where we need to create a calculated column named Expire Date where all of the documents expiration will be a year from the modified date [Modified].

    We're able to create it but has an accurate result showing 1900 date. We tried to refresh the calculation and it showed correct value again. Hours after, it went back to 1900 date.

    Everytime we edit the calculation and save it, it shows correct result but issue went back hours after. Right now, it is showing a two year Expiration Date though formula is [Modified] + 365.

    one thing I noticed is: almost every files are showing inaccurate results except for .csv, .txt, and images file type.

    Please see images below:

    Another concern in the calculation is when I first select the Modified column, it shows with a bracket, [], but when formula is revisited, the bracket is not there.

     

    Here is the result:

    First Date column is Modified Date second Date column is Expiration Date 

     


    Friday, January 6, 2012 3:54 AM

All replies

  • Hi,

    After updating the Formula for the Expire Date, it shows correct result again but approximately 2 hours after, it went back into the 1900 Date result. Please advise, thank you.

     

    in addition, leap year computation is not included in this formula:

    =DATE(YEAR(Modified)+1,MONTH(Modified),DAY(Modified))


    Any inputs please?

    thanks.

    Monday, January 9, 2012 6:44 AM
  •  

    Hi Jerson:

     

    About your issue, I tried, but failed to reproduce it.Through the results “12/30/1900”,maybe the formula column can’t get Modified column’value after 1 hour.

    In order to verify this speculation, you can check it by  two ways as following:

        

         1 create a calculated column named test1, the formula is: = Modified. After waiting one hour to view this column is displayed correct value or not.

         2create a date column named testDate1, assigned current time. Then create a calculated column named test2, the formula: = testDate1 +365. After waiting one hour to view this column is displayed Correct value or not.

     

    If by the first method does not display properly, while the second way to display properly, or does not display either way. you need to check your list  if have an impact custom content or not. For example (custom workflow, ect).

    If it can be correct  displayed both ways, you can try clearing your browser cache and try again.

     

    Thanks,

    Lhan Han 

     




    • Edited by Lhan HanModerator Thursday, January 12, 2012 1:48 AM
    • Marked as answer by Wayne Fan Friday, January 13, 2012 1:47 AM
    • Unmarked as answer by Mike Walsh FIN Thursday, January 19, 2012 11:49 AM
    Monday, January 9, 2012 11:27 AM
    Moderator
  • Hi Lhan,

    Thanks a lot!

    I really did a great progress using that method!

    We now found out that the sudden changes on the results is due to the crawlings set in our site. Once crawl is executed, results changed suddenly.

    Full crawl is every 12:00 AM and Incremental crawl is every 2 hours

     

    Do you know any way how to maintain the accuracy of the result without changing the crawling schedule?

    Are there some sort of .bat or refreshing app pools or other way to sort this out?

    it would be better if there is a way to resolve in the site settings before moving to the server changes.

    Thanks!

    Wednesday, January 11, 2012 6:54 AM
  • You should be able to exclude your calculated column from being indexed in the Document Library Settings.  Might be worth giving this a try.
    Steven Andrews | SharePoint Professional | http://www.twitter.com/backpackerd00d | https://baron72.wordpress.com/
    • Marked as answer by Wayne Fan Friday, January 13, 2012 1:47 AM
    • Unmarked as answer by Mike Walsh FIN Thursday, January 19, 2012 11:49 AM
    Wednesday, January 11, 2012 11:48 AM
    Answerer
  • Hi Steven,

    Thanks for your help.

    However, as I tried to do so, the calculated column is excluded by default.

    Calculated column's name is Expiry Date.

    Please help. 

    Thursday, January 19, 2012 11:46 AM