Using 10/1/2008 as the "HireDate" field, the calculated "Number of Years" returns 9 years when it should clearly be 10 years.

Using 8/1/1988 as the "HireDate" field, the calculated "Number of Years" is 29 years when it should be 30.

Using 11/1/1983 as the "HireDate" field, the calculated "Number of Years" is 34 years when it should be 35 years.

I was thinking about just adding 1 to the year value but then people who have not worked a year yet would show as working for a year.

I really don't know what is happening. Here is the formula:

=IF(DATEDIF(HireDate,NOW(),"y")=0,"< year","")&IF(DATEDIF(HireDate,NOW(),"y")=1,"1 year","")&IF(DATEDIF(HireDate,NOW(),"y")>1,DATEDIF(HireDate,NOW(),"y")&" years","")
  • Actually when i was finding the years between two dates.....=DATEDIF([Column1], [Column2],"y"). This worked for me. Not sure why it is not working for you. – Ganesh Sanap Oct 1 '18 at 17:31
  • Your formula as written also worked for me. I wonder if perhaps the Time portion returned by the NOW() function is messing with your return values (for example, not returning a full 1 for counting today). Have you tried changing the NOW() function to TODAY() instead? – C. Dennett Oct 1 '18 at 17:44
  • Using TODAY() instead of NOW() worked! – jarobinson Oct 1 '18 at 17:56
  • Please post and accept the answer to your question citing C. Dennett's response. That way the question shows as answered. Thanks! – Chris Fells Oct 1 '18 at 22:34
  • This worked, but it's not the root problem. See my answer. – jarobinson Oct 2 '18 at 13:01

Use TODAY over NOW

   =IF(DATEDIF(HireDate, TODAY(),"y")=0,"< year","")&IF(DATEDIF(HireDate, TODAY(),"y")=1,"1 year","")&IF(DATEDIF(HireDate, TODAY(),"y")>1,DATEDIF(HireDate, TODAY(),"y")&" years","")

Today vs Now

  • TODAY uses DATE
  • NOW uses DATE + Time
| improve this answer | |
  • Again, this worked, but it's not the root problem. See my answer. – jarobinson Oct 2 '18 at 13:02

I've done some research and discovered that calculated fields don’t update themselves until item itself is updated. So even if there is a formula in the calculated field by the next day it's already outdated and it won't recalculate until you update the field manually, simply open save and close. This is simply the way SharePoint works and I will have to find another way to represent this, maybe with a "view" that updates every time you look at it, I'm not sure.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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