LinkBack URL
About LinkBacks
Dear Excel expert person,
Could you please advise me how can convert number data to Month and year?
I have data is 27.03.2014 and would like to change this value is Mar-2014.
which formula should be work?
Best regards.
Dear Excel expert person,
Could you please advise me how can convert number data to Month and year?
I have data is 27.03.2014 and would like to change this value is Mar-2014.
which formula should be work?
Best regards.
Is the original date text or a number? If it is text do the days have leading zeros (ie. is 1 March 2014 shown as 01.03.2014 or 1.03.2014)? Do you want the result as a number or as text?
Please add reputation by clicking on the * if I have helped.
Please mark the thread SOLVED if your issue has been resolved.
Thanks, Glenn.
change the format, there are many options under Date to choose from, if what is there is not what you want, create your own format under Custom
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
Attached please find my example data of Excel file
And I try to change format but does not work.
please help me and advise me.
Convert to Date.xlsx
Try this
in B1
=TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"mmm-yyyy")
27.03.2014 Mar-2014
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Your dates are not real dates, they are text looking like dates. Convert with this and then format...
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))
@ AlKey, I feel its always better to have dates AS dates where possible, that way you can use them in calcs. For instance, if you try to add 1 to your answer(Feb-2014), you get 2/2/2014, instead of 2/28/2014
Last edited by FDibbins; 06-12-2014 at 12:34 AM.
In B2 try this:Formula:
Please Login or Register to view this content.
To work properly it will require the original dates to have leading zeros on the days. Your example does not show a day prior to the 10th of a month to see it that is true or not.
Formula:Please Login or Register to view this content.
i used 1 instead of actual date because thats what your example showed?
unsure if thats what you actually want though
alternatively you can text to column>date>DMY
Last edited by humdingaling; 06-12-2014 at 12:34 AM. Reason: additional data
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thank you for the feedback!
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
why not just
=--SUBSTITUTE(A2,".","/")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks