Solved

Excel -- Date Format

Posted on 2011-09-26
10
250 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now