Solved

Excel -- Date Format

Posted on 2011-09-26
10
290 Views
Last Modified: 2012-05-12
How can I change the attached Excel file format to "MM/DD/YYYY" so cell A3, etc displays as 9/19/11 ?
Book2.xlsx
0
Comment
Question by:finance_teacher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 100 total points
ID: 36599015
Not as you have the data entered.  Your data is TEXT and Dates are actually numbers.  You can format the cell as date = mm/dd/yy but, in your data, Excel will not recognize/translate the text into a date.
If you format the cells as date = mm/dd/yy and enter legitimate dates - the format will appear correctly from now on.
You will have to create a function that changes these current text strings to your date format.
Scott C
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 100 total points
ID: 36599017
finance_teacher,
I think this formula will to the trick.

=DATE(20&RIGHT(A3,2),LEFT(A3,2),MID(A3,3,2))

Kyle
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 36599028
Try this.

Copy this to your B3 cell and see what happens.

=LEFT(A3,2) & "/" & MID(A3,3,2) & "/" & RIGHT(A3,2)

Scott C
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 50

Accepted Solution

by:
barry houdini earned 300 total points
ID: 36599049
You don't need a formula you can convert "in situ" using "text to columns" functionality

Select column of dates then:

Data > Text to Columns > Next > Next > under "column data format" select "date" and from the dropdown select MDY > Finish

Now they are true dates you can format as required

regards, barry
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 36599052
You can make a function that will do this for your entire list of values - replacing the original values with the new ones.
Once changed and you format your cells to your date preference, your good.
Now, it this is the way the data will continue to be entered, than using this previous function is what you may want to do.

Scott C
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36599188
@Barry,
Interesting, I've never used text-to-columns that way.  I never even noticed the date format.  It seems to be kinda sensitive though.  I tried using this technique with the number 9192011 and a format of MDY, it didn't work.  Then I tried it with the number 20110919 and the format YMD, it did work.  What exactly are the limitations?

Thanks for teaching me a new trick:)

Kyle
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36599285
Hello Kyle,

To be honest I don't know why 9192011 doesn't work - I think it works for 91911 so I guess it's the 4 digit-year that isn't expected with that format (whereas it would be for YMD). It normally works where the data is "recognisably a date". Note that if the dates are in a valid date format for your region but formatted as text, e.g. 25/12/2009 in the UK but text-formatted then you can use the same trick but you only need

Data > Text to columns > Finish

formula-wise if your regional settings default to m/d/yy then you can use this formula

=TEXT(A3,"00-00-00")+0

format as date

regards, barry
0
 

Author Comment

by:finance_teacher
ID: 36599481
Steps
1.      Highlight column A
2.      Data, Text to Columns
3.      Next
4.      Next
5.      Select the “Date” radiobutton
6.      Finish
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36599491
Barry,
Thanks for the explanation and the formula.  I like the sneaky use of adding zero to force coercion into a date.  Good one!

kyle
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36599644
Hello finance teacher - those steps were almost as I detailed in my initial answer - I'm not clear, are you suggesting that worked or querying the steps? There's one missing step - after selecting date you need to select "MDY" from the dropdown if that isn't already selected. Note the format MDY applies to the "source format" of the dates

regards, barry
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question