We have a field called, "Reporting Date" and I am having trouble creating a calculated field to return whether it is Year7 or Year8.


If 'Reporting Date' is between 2/9/2018 and 2/8/2019 then it is Year7. 2/9/2019 to 2/8/2020 would be Year8.

This is the formula I have right now.

=IF(AND([Reporting Date]>"2/8/2018",[Reporting Date]<"2/8/2019"),"Year7","Year8")

I didn't get any syntax error but I have documents that are in between those dates and also documents that are after the dates. However, I am getting only Year8s or something only Year7s when I was playing around with it.

What did I do wrong here...?!

1 Answer 1


SharePoint calculated fields uses formula like in Excel. You can try using this formula:

=IF(AND([Reporting Date]>=DATEVALUE("2/8/2018"),[Reporting Date]<=DATEVALUE("2/8/2019")),"Year7","Year8")

I have tried in one of my list and it looks like below:

enter image description here

  • It worked like a charm...Amazing! I have to study your formula and I have a question about 'DATEVALUE'. So is 2/8/2018 considered as a Text? - what does 'DATEVALUE' do? Thank you so much!! I am learning a lot from this!
    – Masarubae
    Jun 5, 2019 at 18:44
  • 1
    The DATEVALUE function converts a date that is stored as text to a serial number that SharePoint recognizes as a date. Details: support.office.com/en-us/article/…
    – UBK
    Jun 5, 2019 at 19:42

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.