0

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

Situation:

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...?!

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
2
  • 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! Commented 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/… Commented Jun 5, 2019 at 19:42

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.