LinkBack URL
About LinkBacks
Hi all,
I would like to convert the dates in the Sheet 1 into the format Month - Year so i can run a pivot Table.
However when i do it some of the dates remain in the current format.
Can you please support
Hi all,
I would like to convert the dates in the Sheet 1 into the format Month - Year so i can run a pivot Table.
However when i do it some of the dates remain in the current format.
Can you please support
In the cells that wont change there is a space before the date. Remove the space in those cells and it should work
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
Dates are numbers
Your dates are a mixtures of numbers and text, ie some of your dates are not dates, e.g. A423
in B2
=A2+0
Copy column B to column A
Now format column A as you want.
Delete column B
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Hi,
Also does not work, it worked for example CELL A2432 returns # value!
Both suggestions will work, however mine will take longer.
With Special-K's suggestion, copy and paste. Do not cut and paste. Also, when you paste, select paste values.
Good point about the paste values!Originally Posted by gmr4evr1
Must admit I'm not quite sure why the OP would need an entire list of dates for a year.
What happens when they get updated? A better solution would be a start date in A2 followed by =A2+1 copied down.
Last edited by Special-K; 03-23-2017 at 09:57 AM.
Another good pointA better solution would be a start date in A2 followed by =A2+1 copied down.
1. i can see no extra space. 2. =A2+0 does not work for all cells. This is a bit heavy handed, but does work:
=IF(ISERROR(A2+0),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)),A2)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I'm not sure now that it was a space issue. I checked again by double clicking in the cells that didn't change, then hit enter or clicked in the next cell and it changed to the format OP wanted. Not sure why it would work that way.
I had a similar odd issue over the last few days where a formula worked perfectly here, but not on the Poster's PC. Excel can be a bit odd sometime... for something that is supposed to be logical. Maybe it's a she....
lol....another good point
Glenn sssshhhhh
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
try this also
=TEXT(A2,"mmmm")&" "&"-"&YEAR(A2)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks