Excel YEAR function not working

Excel YEAR function not working

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.

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.