Solved

Excel -- Date Format

Posted on 2011-09-26
10
260 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Outlook Free & Paid Tools
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

919 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

17 Experts available now in Live!

Get 1:1 Help Now