[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Excel -- Date Format

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
finance_teacher
Asked:
finance_teacher
  • 3
  • 3
  • 3
  • +1
3 Solutions
 
clarkscottCommented:
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
 
kgerbChief EngineerCommented:
finance_teacher,
I think this formula will to the trick.

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

Kyle
0
 
clarkscottCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
barry houdiniCommented:
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
 
clarkscottCommented:
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
 
kgerbChief EngineerCommented:
@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
 
barry houdiniCommented:
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
 
finance_teacherAuthor Commented:
Steps
1.      Highlight column A
2.      Data, Text to Columns
3.      Next
4.      Next
5.      Select the “Date” radiobutton
6.      Finish
0
 
kgerbChief EngineerCommented:
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
 
barry houdiniCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now