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
Solved

Excel -- Date Format

Posted on 2011-09-26
10
273 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
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

856 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