LinkBack URL
About LinkBacks
Hello. I'm wondering if I can enter 3/9/16 and get 03-Sep-16 and not 09-Mar-16 as I'm currently getting.
Hello. I'm wondering if I can enter 3/9/16 and get 03-Sep-16 and not 09-Mar-16 as I'm currently getting.
You can try this one.
format cell as dd-mmm-yy instead of mm-dd-yy
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi, ph11,
IN the cell(s) you want to enter your dates check the formatting... Do Ctrl-1 and choose the NUmber tab. If it says d/m/y you'll get the date you want. Look under Date (in Category) but look under Custom as well where you might come across m/d/y in which case you can change it to d/m/y or any other layout that puts 'd' before 'm', unlike the preference in the States to have 'm' first...
Many thanks for the quick response.
I tried adding a custom entry of dd-mmm-yy in the "number format" drop down menu under "more number formats" but it still reverses the day and month.
Find and replace "/" to one "space" and use the below formula in next column
=TEXT(DATE(RIGHT(A1,2),MID(A1,FIND(" ",A1,1),FIND(" ",A1,2)),LEFT(A1,FIND(" ",A1,1))),"DD-Mmm-YY")
This worked for me just fine.
Enter formula in B1 and format cell as Custom, dd-mmm-yy
Formula:
Please Login or Register to view this content.
Or maybe a better formula
Formula:Please Login or Register to view this content.
v A B 1 03/09/16 03-Sep-16 2 26/08/16 26-Aug-16 3 23/03/15 23-Mar-15
Last edited by AlKey; 09-04-2016 at 11:25 AM.
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
Thanks again for the responses.
I was actually after the conversion to occur in the cell I entered the data. I thought there may have been an option somewhere within the programme itself.
It's to do with your regional settings in Control Panel - the short date format will be set to m/d/yy (i.e. US format), so you need to change this to d/m/yy.
Hope this helps.
Pete
Embarrassingly easy. Thanks so much.
You're welcome. If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.
Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks