How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9659
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

I want to convert the date display of dd/mm/yyyy to yyy/mm/dd

Customer Question

I want to convert the date display of dd/mm/yyyy to yyy/mm/dd fotmat. How do I do it with MS Office Excel 2007?
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  John D replied 2 years ago.

Hi,

 

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:

yyyy/mm/dd

then click OK

 

 

Customer: replied 2 years ago.

 

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.

 

A

B

23/12/2011

2011/12/23

Regards, XXXXX XXXXX

Expert:  John D replied 2 years ago.

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

 

Customer: replied 2 years ago.
I will send you an extract from the file within 15 minutes. The date formated cells (not text) dd/mm/yyyy need to be converted to yyyy/mm/dd format.
Expert:  John D replied 2 years ago.
sure, let me know if you need help uploading files on this site

 

Customer: replied 2 years ago.

Attachment: 2012-01-06_010446_justanswer_-_2012-01_-_06.xlsx

Sorry I was not able to attach excell document. Below a copy of the excell sheet.

 

JustAnswer
Column A to be converted to column B format without typing addresses into column B.
AB
Date dd/mm/yyyyDate yyyy/mm/dd
01/01/20122012/01/01
01/11/20102012/11/01
01/12/2011
01/12/2011
01/12/2011
01/12/2011
01/12/2011
01/12/2011
02/09/2011
02/12/2011
02/12/2011
02/12/2011
02/12/2011
02/12/2011
02/12/2011
02/12/2011
02/12/2011
03/01/2012
03/01/2012
04/01/2012
04/01/2012
04/01/2012
04/01/2012
04/01/2012
04/12/2011
05/01/2012
05/12/2011
06/04/2010
07/12/2011
09/11/2011
09/12/2011
09/12/2011
10/12/2011
11/07/2011
11/12/2011
12/12/2011
13/12/2010
13/12/2011

Expert:  John D replied 2 years ago.

Ok here you go

 

http://wikisend.com/download/909350/2012-01-06_010446_justanswer_-_2012-01_-_06a.xlsx

 

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

 

Customer: replied 2 years ago.

 

Dear XXXXX,

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.

Yours Sincerely,

XXXX XXXX

South Africa

Expert:  John D replied 2 years ago.

Please don't forget to click Accept and add bonus so I get credit for the time I have spent assisting you

 

 

 

Expert:  John D replied 2 years ago.

Forget it

 

Customer: replied 2 years ago.

Dear XXXXX,

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.

Yours Sincerely,

xXxxx XXXX

South Africa

Customer: replied 2 years ago.

Hi John,

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?

Regards,

Frikkie

Customer: replied 2 years ago.

 

Hi John,

 

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.

 

Yours Sincerely,

 

Frikkie xxxx, South Africa.

Customer: replied 2 years ago.
Dear XXXXX,

If you look at the excel document that I've emailed to XXX@XXXXXX.XXX 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 and I will appreciate it if you could investigate this issue further.

The current solution is not satisfactory and I can therefore not click on “Accept Answer”.

Yours Sincerely,

XXXXX XXXXXXX
Customer: replied 2 years ago.
Dear XXXXX,

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.

Yours Sincerely,

XXXX XXXXX
Expert:  John D replied 2 years ago.

Test file using a button to fix the dates

 

http://wikisend.com/download/508206/2012-01-06_010446_justanswer_-_2012-01_-_06%20a.xls

 

 

Please make sure that you have your Excel Options configured so that macros are allowed to run

 

 

Customer: replied 2 years ago.

John,

 

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.

 

Yours Sincerely,

Frikkie

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 
Chat Now With A Microsoft Office Technician
John D
John D
385 Satisfied Customers
Bachelor of Science - Engineering Consultant