How do I change dates from dd/mm/yy to mm/dd/yy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I cannot change a column to show dates as mm/dd/yy. Some cells will change
but others are frozen on dd/mm/yy. Nothing I do will clear it and lock in
the format I want.
 
Chances are that the ones that are frozen are really text strings, not dates
(you could check by applying the comma style to those cells; if they're
dates, you'll get some really big percents. If not, you still won't see a
change). If that's it, adding 0 to these cells will help Excel convert them
to numbers (and dates are really just specially formatted numbers). Type a 0
in some random cell and copy it. Select the column with the dates and Edit >
Paste Special, select the 'Add' radio button and click OK. Everything will
probably show up as a number in the 30000 range. Now apply your preferred
format.
 
You probably have the illusion of good dates and bad dates, but in fact
are messing up the one that Excel thinks are good dates.

You might want to create another column so that you can see what Excel
really thinks you have.
L4: =B4
M4: =ISNUMBER(B4)

format as with something that you are familiar with i.e. mmm dd, yyyy :
---
Excel uses your Regional Short Date system setting. But since you are having a problem with mixed dates, you might
want to consider changing your format to yyyy-mm-dd which is the ISO standard.

A couple of things that you should be aware of is that VBA is US centric (mm/dd/yy)
so dates will be treated in the US format. If you want to enter a specific date in
VBA it is safest to use the DATE function.

and Excel (not VBA) is "kind" enough to make a bad date become a good date,
so if there aren't 31 months in a year Excel will assume that was days (or years) and
change the date to a valid one -- with dates like 11/12/05 and 12/11/05 Excel
will use your Regional settings in the order you specified.
 
Back
Top