Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel returns 1905 instead of actual year

Posted on 2014-04-03
21
Medium Priority
?
12,386 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 35

Expert Comment

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

HTH,
Dan
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 1000 total points
ID: 39975548
Change formula to:

=YEAR(Your lookup))

Thanks
Rob H
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39975554
@robhenson
=YEAR('25/4/2012')
will return #VALUE if your locale uses m/d/yyyy as date format.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Jagwarman
ID: 39975556
I am using =YEAR(F2) and it returns 1905
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39975567
=YEAR(A1) returns 2012 if A1 is 4/25/2012
0
 
LVL 34

Expert Comment

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

Expert Comment

by:Saqib Husain, Syed
ID: 39975587
Are you using this on a MAC?
0
 

Author Comment

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

Expert Comment

by:Saqib Husain, Syed
ID: 39975982
Can you upload a file showing this?
0
 

Author Comment

by:Jagwarman
ID: 39977553
File with date field in which returns 1905

Regards
Date.xlsx
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39977563
You have the field formatted as Date, change it to number and it shows 2012.
0
 
LVL 35

Assisted Solution

by:Dan Craciun
Dan Craciun earned 1000 total points
ID: 39977568
:)
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 34

Expert Comment

by:Rob Henson
ID: 39977571
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 34

Expert Comment

by:Rob Henson
ID: 39977582
@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
ID: 39977583
Thanks guys appologies for being stupid
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39977586
@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 34

Expert Comment

by:Rob Henson
ID: 39977637
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 34

Expert Comment

by:Rob Henson
ID: 39977646
@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 35

Expert Comment

by:Dan Craciun
ID: 39977650
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 34

Expert Comment

by:Rob Henson
ID: 39977696
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 34

Expert Comment

by:Rob Henson
ID: 39977698
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

782 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