Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I change dates from dd/mm/yy to mm/dd/yy
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. |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I change dates from dd/mm/yy to mm/dd/yy
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. "Dotteee" wrote: 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. |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I change dates from dd/mm/yy to mm/dd/yy
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "bpeltzer" wrote in message ... 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. "Dotteee" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Automatic shading of cells based on dates??? | Excel Worksheet Functions | |||
In Excel spreadsheeet the dates change to the wrong date after we. | Excel Discussion (Misc queries) | |||
Date Comparison Driving Me Crazy | Excel Discussion (Misc queries) | |||
Why does Excel Copy and Paste change dates from the original when. | Excel Discussion (Misc queries) |