I have in column A a series of dates, in long date format, MM/DD/YYYY. In column B I want just the year, so I used for B2; =YEAR(A2) expecting for 08/01/2013 to have 2013 returned. Instead I get #Value!
I also tried having column A as general format. This did not work either. Could this have something to do with my administrators settings? As far as I have experienced what I am doing should work.
I have attached an image of my file for clarification. excel.png
Most likely, your dates are stored as text. Assuming they are in a format compatable with your default date pattern (can't tell from the picture whether it's m/d/yr or d/m/yr or where you are from in your profile).
A picture does not help us troubleshoot usually. To attach a spreadsheet, Go Advanced>Manage Attachments.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
If you're getting a #VALUE! error that means your dates are not true Excel dates. They're probably TEXT strings that look like dates.
What version of Excel are you using?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Valko - I am using version 2010.
Chemist B - the format is m/d/yr
I have attached a sample spreadsheet (I can't attach the actual file due to confidentiality issues).
OK, your dates are really TEXT values.
This worked in the sample file...
Select the range of dates
Goto the Data tab>Text to Columns
Click: Finish
Now you can format the dates in the desired style.
Or if you may not change the cells:
=right(a1,4)
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Thanks so much everyone.
I ended up using Popipipo's recommendation as changing the format of the cells to be an actual date still didn't yield what I wanted using the Year Function. I was getting an output of 1905 rather than the desired 2013.
Cheers,
If you result is 1905 your celformat is 'date'
Change it to 'number' and you get 2013
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks