# Column calculated to return the year of another column

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:

``````=YEAR("JAN/15")
=YEAR("MAR/15")
``````

Fails:

``````=YEAR("FEV/15")
``````

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