finance_teacher
asked on
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
Book2.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
@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
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
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
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
ASKER
Steps
1. Highlight column A
2. Data, Text to Columns
3. Next
4. Next
5. Select the “Date” radiobutton
6. Finish
1. Highlight column A
2. Data, Text to Columns
3. Next
4. Next
5. Select the “Date” radiobutton
6. Finish
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
Thanks for the explanation and the formula. I like the sneaky use of adding zero to force coercion into a date. Good one!
kyle
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
regards, barry
Copy this to your B3 cell and see what happens.
=LEFT(A3,2) & "/" & MID(A3,3,2) & "/" & RIGHT(A3,2)
Scott C