SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to get the difference of two date fields in terms of years. SP keeps giving syntax error. Any pointers where I'm wrong?

=DATE(YEAR([Awarded Project End Date])-YEAR([Awarded Project Start Date]))
share|improve this question
up vote 1 down vote accepted

If you are returning a date then you need to pass a YEAR, MONTH and DAY. Right now you are subtracting two years and trying to return the result as a date which is not valid. Try something like this:

=YEAR([Awarded Project End Date])-YEAR([Awarded Project Start Date])
share|improve this answer
This worked. I needed this to define number of fields to be shown using jquery – SP_Days May 23 '12 at 4:04

Year() returns an integer and you cannot convert that into a DATE(). Try this instead :

=DATEDIF([Awarded Project End Date], [Awarded Project Start Date],"y")

There is more info available on MSDN.

share|improve this answer
I used this formula before, I was passing the answer as a number. Good to know. Thanks! – SP_Days May 23 '12 at 4:03

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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