Hi all
I'm having trouble with dates in excel. I have a list of dates in the format 03/29/2005 and want to output the year only from this. using the command:
=REPLACE(A1,1,6,)
works for most of them, but not if the date has a zero in it (eg for march=03). Any ideas?
Thanks
Kate
Hi Kate,
How about:
Cheers,=RIGHT(A1,4)
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
Why not just format as "yyyy". If you actually want the year, then create a helper column that uses
=Year(A1)
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
I guess it depends if "mm/dd/yyyy" is in your operating system as a recognized date format.
In my case it is not, therefore Excel thinks "03/29/2005" is a text string.
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
mm/dd/yyyy would be a Custom Format. If it is formatted as text then it would be a string.
All Excel dates are actually numbers, appearance is achieved by Formatting. If you enter today's date then format it as a number it will be 40759.
That's why I would use the YEAR() Function, or a custom format of yyyy
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
If you're looking for a text value you could use =TEXT(A1,"YYYY"). If you only want it to be a number with no association to a date then =VALUE(TEXT(A1,"YYYY")).
Last edited by gjlindn; 08-04-2011 at 03:01 AM.
-GregIf this is helpful, pls click Star icon in lower left corner
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks