1

I'm trying to create a new column in a shp list that contains the difference in days between two other columns in that list. These two columns are the custom list columns that contain the date Created on and Modified on for each list item.

So far I have tried the solution presented by MS found here: http://msdn.microsoft.com/en-us/library/bb862071%28v=office.14%29.aspx

However, when I use following code and settings to calculate my column, the system presents me with an error:

ColumnDif: Formula:

=DATEDIF(modified,created, "d")

with formula setting: may only return date and time.

I tried to change the formula setting to text and number with same code, but that did not work either.

I also tried the other code, that is used for time formats, but that does not work either:

=TEXT([modified]-[created],"h")
Improve this question

3 Answers 3

Reset to default
1

Problem solved: Since I'm working with a dutch setup of SharePoint it never occurred to me to use dutch formulas. When I changed DATEDIFF to the dutch equivalent DATUMVERSCHIL it works like a charm. Very strange though, since some English formulas work, and not this.

Improve this answer
0

Try this (you need to use brackets []): 

=DATEDIF([Created],[Modified],"d")

and for the data type returned from this formula use "Single line of text" or "Number"

Improve this answer
6
  • I tried your solution, but it did not work. When I use the code: =int(([Created]-[Modified])*24) I can save the column without error, however the results are a bit disappointing. I get -1 in each column, so this is not right either. I should get the difference in time in hours then.
    – Dafalgor
    Commented Apr 24, 2013 at 6:32
  • Also, when SHP saves code, the brackets have disappeared on returning to the column settings page. So I do not think they are necessary.
    – Dafalgor
    Commented Apr 24, 2013 at 6:41
  • "but it did not work" - Did you mean that you couldn't save formula or it returned incorrect result? Also, in your question you asked about "difference in days". As I'm seeing now you need to get difference in time in hours. In that case second formula that you provided (=TEXT([modified]-[created],"h")) have to be fit for you. For example, if item Created at "4/23/2013 5:58 PM" and Last modified at "4/24/2013 11:15 AM" result will be "17" becasuse it is 17 hours between that dates. Does this example match your's expected behavior?
    – Andrew
    Commented Apr 24, 2013 at 8:33
  • To answer your first question, it would not save. Considering the problems I'm having I am trying anything to get any result. That is why I tried to get the difference in hours, as well as in days. Your new approach with =TEXT([modified]-[created],"h") also throws me the same error.
    – Dafalgor
    Commented Apr 24, 2013 at 8:59
  • Can you, please, provide text of the error?
    – Andrew
    Commented Apr 24, 2013 at 9:07
0

"with formula setting: may only return date and time."

Do you mean that your calculated column is set to return a date? The number of days between two dates is a number, not a date/time. The Datedif formula is correct with the built in Created, Modified columns and the return type set to number.

Improve this answer
1
  • Yes, however, I tried all possible settings already. With each of them it is the same issue.
    – Dafalgor
    Commented Apr 24, 2013 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.