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!
