I have a list that has a column calculated based on another column. [Reference Month] > option type (JAN/15, FEV/15, MAR/15, ..., JAN/16, FEV/16, MAR/16, ..., JAN/17, FEV/17, MAR/17, ...) [Reference Year] =YEAR([Reference Month]) Until some time ago this formula was working normally. But every item included it returns the year 2017 to the Reference Year column, even selecting months of 2015 or 2016. And many items that were previously correct, have Reference Month 2015 and 2016, but now are with Reference Year 2017. How do I solve this? Is there any other way?

  • Can you list all involved Fields, the datatype and the Formulas used – Danny '365CSI' Engelman Mar 8 '17 at 20:40
  • involved Fields: Reference Month > datatype: option (menu 4 choose); Reference Year > datatype: calculated, Formula: =YEAR([Reference Month]) – Jean Leles Mar 9 '17 at 12:37
  • So ReferenceMonth is a String.. NOT a Date value.. the YEAR Function expects a Date.. not a String, and will hapilly produce incorrect values for you – Danny '365CSI' Engelman Mar 9 '17 at 13:05

Produces 2015:




SharePoint is smart enough to convert English dates.. but it can't proces any other language

You have to add something like:

=2000 + MID( "FEV/17" , 5 , 2 )
  • @danny-365csi-engelman I understand now, thanks. With formula =2000 + MID( "FEV/17" , 5 , 2 ) worked out. But I have another problem now, in the list in production environment I use the complete month, examples: January/15, February/15, March/15. How do I get only the last 2 characters of the Month Reference? [I'm a beginner, sorry!] – Jean Leles Mar 9 '17 at 13:37
  • Based on your formula I was able to correct this problem using the formula as follows: =2000+EXT.TEXT([Reference Month];(LEN([Reference Month])-1);2) worked out!!! Thank you very much, @danny – Jean Leles Mar 9 '17 at 14:45
  • Did you do this EXT.TEXT in SharePoint ??? Doesn't work for me.. All that do work in SP I know: viewmaster365.com/365coach/#/Calculated_Column_Functions_List – Danny '365CSI' Engelman Mar 9 '17 at 15:15
  • Yes! Actually the function is EXT.TEXTO that is equal MID function, but in Portugese. I put in formula MID, when I click in OK Sharepoint convert MID to EXT.TEXTO, I think so because my Sharepoint's Language is Portuguese. – Jean Leles Mar 9 '17 at 17:31

Try to use this formula to get the Year of a DateTime column

=TEXT([Reference Month],"yyyy")
  • Thks! But with this formula the result is the year 2017 to. I need that when selecting Reference Month JAN/15 the Reference Year is 2015. – Jean Leles Mar 8 '17 at 20:55

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.