locked
Bad Date Calculation with SharePoint Calculated Columns RRS feed

  • Question

  • Found something finicky and wanted to let others know about it and see if there was a better solution to the problem.

    Say you wish to know the time a task was not completed, let's use the Created and Modified fields. If you set a calculated column to produce an integer (number of days it takes to complete a task) you can set the field to "=[Modified]-[Created]". This works great if you create the column, but values go wrong if the column is left alone. Specifically, what I found the calculated column was doing....

    • If the column is left alone, it will subtract the dates and then convert it to an integer.
    • If the column is updated (edit the column, do not modify anything and select "OK"), it will convert the dates to integers and subtract them (desired result).

    For example, if I have pre-existing data and create a column....

    Created Modified DaysTaken
    2011-01-01   2011-01-02   1

    My calculated column we be able to determine that this was 1 day (when the column is created). But say a new task is created, you would get the following data...

    Created Modified DaysTaken
    2011-01-01   2011-01-02   1
    2011-01-02   2011-01-03   48,364

    So this is directly subtracting dates (as SQL would do) without first converting to integers, producing a date which is then converted to an integer. But if I open up the calculatedcolumn's properties and just hit OK, all of the items are recalculated correctly.

    To work around the issue, I simply created a column for each the Created and Modified fields to convert to integers and then subtract the resulting numbers. Let me know your thoughts!



    • Edited by VisualBacon Monday, October 31, 2011 4:40 PM
    Monday, October 31, 2011 4:39 PM

Answers

  • I would use the built in formula:

    =DATEDIF([Column1], [Column2],"d")

    For reference, here's the published article

    http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx


    -victor

    Please remember to indicate if your question/comment has been answered.


    Monday, October 31, 2011 6:15 PM