Solved

Excel returns 1905 instead of actual year

Posted on 2014-04-03
6,187 Views
I have a look ups on cells and the result are  25/4/2012, 26/4/2012 etc I need to turn this into a Year so that when I create a Pivot it Groups all items into the year and just counts the number of items per year.

Is there a way to make excel return 2012?

Thanks
0
Question by:Jagwarman
• 9
• 6
• 4
• +1

LVL 34

Expert Comment

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

HTH,
Dan
0

LVL 31

Accepted Solution

Rob Henson earned 250 total points
Change formula to:

Thanks
Rob H
0

LVL 34

Expert Comment

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

Author Comment

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

LVL 34

Expert Comment

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

LVL 31

Expert Comment

What is in F2 for it to return 1905?
0

LVL 43

Expert Comment

Are you using this on a MAC?
0

Author Comment

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

LVL 43

Expert Comment

Can you upload a file showing this?
0

Author Comment

File with date field in which returns 1905

Regards
Date.xlsx
0

LVL 31

Expert Comment

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

LVL 34

Assisted Solution

Dan Craciun earned 250 total points
:)
You formatted that field as date, that's why you get the weird result.
Make it a number, and it will be 2012.

HTH,
Dan

LE: 65 seconds late...
0

LVL 31

Expert Comment

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
0

LVL 31

Expert Comment

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
0

Author Comment

Thanks guys appologies for being stupid
0

LVL 34

Expert Comment

@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.
0

LVL 31

Expert Comment

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
0

LVL 31

Expert Comment

@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
0

LVL 34

Expert Comment

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.
0

LVL 31

Expert Comment

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.
0

LVL 31

Expert Comment

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
0

Featured Post

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls â€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.