Select the cell in question, press Ctrl + 1 , select Custom from the Categories list (under the Number tab), then in the field under Type: , enter the following:
then click OK
Dear Sir, your answer is not accepted because it is not a resolution for my question; unless I do not understand your answer.
Please take note that my understanding of your answer is that it merely formats a new cell as a date displayed as yyyy/mm/dd and not converting an already existing cell with a date format of dd/mm/yyy to a yyyy/mm/dd format.
If for example one would take columns A & B below:
* Column A's cell is formatted to display the date as dd/mm/yyyy.
* Column B's cell is formatted to display the date as yyyy/mm/dd.
If one selects column A's cell and change the format to yyyy/mm/dd it does not automatically change it to 2011/12/23.
Regards, XXXXX XXXXX
In that case the date in the cell has not been entered as a read date but as TEXT that looks like date
If you need further assistance with this send me your file so I can write the proper formula to convert that text
Sorry I was not able to attach excell document. Below a copy of the excell sheet.
Ok here you go
As I tried to explain to you, the dates in column A were not real dates. Had to renter them one by one so the other column could read them as dates
If you look at the excel document that I've emailed to XXXXX@XXXXXX.XXX you will see that the dates in column A is not in text format but in dd/mm/yyyy date format.
To re-enter each date in column B to get it in yyyy/mm/dd format is not the solution I was hoping for, but it seems to be the only solution. If so, it is a major shortcoming in MS Office Excel 2007.
Please don't forget to click Accept and add bonus so I get credit for the time I have spent assisting you
Let us see what solution you and your colleagues can present in the next 5 days. If there is no solution other than re-enter dates, please obtain written confirmation from Microsoft that there is no other solution for the conversion of dates.
I will have no problem in "Accepting Answer" once I get this assurance.
It is not my intention to withhold any payment from you, but please give it another go!
I've also spend a lot of time on this. It is now 04:00 in South Africa.
Are you going to attempt to get a solution for the date format conversion from dd/mm/yyyy to yyyy/mm/dd other than re-entering the dates manually?
I will not up the price as requested by you. You could not come up with a solution to my question and now you want me to up the prise for a possible solution.
Have a nice day.
Frikkie xxxx, South Africa.
Test file using a button to fix the dates
Please make sure that you have your Excel Options configured so that macros are allowed to run
According to Microsoft, macros should not be enabled unless it is "digitally signed macros". I've selected this option but the program does not run and it thus appears that I can't use your proposed solution.
Thanks for your effort, but it seems that I rather have to explore other channels to solve my question, i.e. to convert date formats from dd/mm/yyyy to yyyy/mm/dd in excel 2007 documents.