Excel returns 1905 instead of actual year

Yup. Store your dates as m/d/yyyy or change the locale of your computer.

HTH,
Dan

@robhenson
=YEAR('25/4/2012')
will return #VALUE if your locale uses m/d/yyyy as date format.

I am using =YEAR(F2) and it returns 1905

=YEAR(A1) returns 2012 if A1 is 4/25/2012

What is in F2 for it to return 1905?

Are you using this on a MAC?

F2 = 24/04/2012
it is being run on a PC not a MAC

Can you upload a file showing this?

File with date field in which returns 1905

Regards
Date.xlsx

You have the field formatted as Date, change it to number and it shows 2012.

Excel works dates as a serial number representing number of days since 1 Jan 1900. Day 2012 after 1 Jan 1900 was 4 July 1905 hence why you think you're seeing year 1905.

Thanks
Rob H

@Dan Craciun, your earlier comment about getting #Value if "mm/dd/yyyy" locale.

If you were in a "mm/dd/yyyy" locale your result wouldn't be '25/04/2012', it would be '04/25/2012' so excel would still recognise it and return the right result.

Thanks
Rob H

Thanks guys appologies for being stupid

@robhenson
Nope.
If I input 25/04/2012 Excel will not recognize it as a date (even when specifically formatted as date), so any function that expects a date will return #VALUE.

Dan, I assume you use mm/dd/yyyy locale settings. I wouldn't expect 25/04/2012 to be recognised as a date in your locale because it would try and see the 25 as a month which it obviously can't.

If you are in a mm/dd/yyyy locale, why would you input 25/04/2012 as it would be contrary to what you would expect, you would enter or expect result of formula to be 04/25/2012?

It would be like me inputting 04/25/2012 in a dd/mm/yyyy locale and expecting excel to recognise as a date.

Thanks
Rob H

@jagwarman

Other alternative for your question, set the formula to just =F2 and format such that it only shows year; custom format "yyyy".

Thanks
Rob H

I agree with you that Excel shouldn't be expected to guess the date format. But I've always wanted for it to show some kind of error on date fields that it can't interpret. You have no visual clue if that field is a correct date or not.

As to why would you input? Simple: you get a csv from someone that uses a different date format. Don't pay attention to that and you're in for some strange behavior.

I totally agree that it is annoying that Excel does not give some sort of error message when it has not been able to complete an "expected" action such as recognition of a date.

I also agree that you have to be mindful of the source and format of any data received. I currently work in the UK for a US based company so when sharing data with colleagues in Philly we have to be careful that we understand the formatting re dates, time zones and currency.

Scratch my comment about just using =F2 and formatting as 'yyyy'. The grouping by year in the Pivot wouldn't work then.

However, I have seen grouping and/or filtering of dates in a pivot without having to create separate columns.

Thanks
Rob H