The potential issue with the Excel YEAR function not working is most likely linked to the possibility that the cell you have referred to is NOT a valid Excel date. This is possible even if you are convinced that you are seeing a valid date. This would equally apply to the MONTH, DAY and in fact any of the date functions.
3 Reasons the Year Function is not working in Excel
Typically you would get a VALUE error and this is Excel telling you that the cell it is looking at is NOT a value and it expects a date to be a value.
We need to step back a moment to understand how dates work in Excel.
If you have a valid date and you format it as a general number you will see something like 40 567. This is what Excel uses as a date and it means that the date is 40 567 days from the 1st January 1900. The fact that you format it into a proper date format is only for humans benefit. Excel is happy with the number. In fact it has to have a number!
If Excel doesn’t recognise the date as a date (e.g. I use American format of mm/dd/yyyy instead of what my computer needs of dd/mm/yyyy) it becomes text and no matter what you do it will not allow any date functions to work. So what you first have to do is check whether the cell is a valid date. The easiest way to do this is to try and change the format of the cell to a different date format.
So the steps would be.
- Click on the cell that contains the ‘date’
- Right click and choose the ‘Format Cells’ option
- Click on the date category
- While watching what happens in the Sample section, alternate between the different date formats (25/12/2014, 25 Dec 2014 etc)
- If the sample changes to the format you have clicked on it is a date
- If it does not change it is NOT a date.
Before any of the date functions can work you need to convert it to a valid date. There are many ways to do this covered in the Handling Dates section.