Solved

Excel returns 1905 instead of actual year

Posted on 2014-04-03
21
6,187 Views
Last Modified: 2014-04-04
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
Comment
Question by:Jagwarman
  • 9
  • 6
  • 4
  • +1
21 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Yup. Store your dates as m/d/yyyy or change the locale of your computer.

HTH,
Dan
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 250 total points
Comment Utility
Change formula to:

=YEAR(Your lookup))

Thanks
Rob H
0
 
LVL 34

Expert Comment

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

Author Comment

by:Jagwarman
Comment Utility
I am using =YEAR(F2) and it returns 1905
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
=YEAR(A1) returns 2012 if A1 is 4/25/2012
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
What is in F2 for it to return 1905?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Are you using this on a MAC?
0
 

Author Comment

by:Jagwarman
Comment Utility
F2 = 24/04/2012
it is being run on a PC not a MAC
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Can you upload a file showing this?
0
 

Author Comment

by:Jagwarman
Comment Utility
File with date field in which returns 1905

Regards
Date.xlsx
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
You have the field formatted as Date, change it to number and it shows 2012.
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 250 total points
Comment Utility
:)
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

by:Rob Henson
Comment Utility
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

by:Rob Henson
Comment Utility
@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
0
 

Author Comment

by:Jagwarman
Comment Utility
Thanks guys appologies for being stupid
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
@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

by:Rob Henson
Comment Utility
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

by:Rob Henson
Comment Utility
@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

by:Dan Craciun
Comment Utility
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

by:Rob Henson
Comment Utility
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

by:Rob Henson
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now