Unable To Change Date Format In Excel 2000 1

[Kevsim]

Using Excel 2000, I wish to format the date as dd/mmm/yy, no matter what I do, the format comes out as M/dd/yy. I checked date format in Control Panel Regional Settings, it was set for dd/mmm/yy. I tried to write to the cell in VB, it did not help.
I would appreciate some advise.
kevsim

 

[xlbo]

format>cell>custom enter dd/mmm/yy

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Kevsim]

This may clarify a little further. I have data dumped from a database program (not Microsoft) into an Excel worksheet. The dates in the database program use a “Period” as a delimiter. When the data is in Excel, the date fields are not recognised as a date field, no matter how I try to format it. What I have discovered is if I manually remove the “periods” and replace them with a “slash”, the date field can be formatted any way you wish. I will say at this time, in the database program, the dates are entered in dd/mm/yy format. After what I tried above, I then selected a total column, then, Edit/Find, for find I used a “period” and replace all a “slash”, it worked OK. I then recorded this as a macro, when I run the same actions as a macro, the day and month are reversed. I have also placed formatting dd/mm/yy to try and stop this happening. Why would it reverse in the macro and not when doing manually. I would appreciate some advise.
kevsim

 

[xlbo]

You need to format AFTER converting the text to dates.

When the data is entered initially, it is recognised as text. When you then do a find/replace, excel recognises the data as dates and immediately tries to coerce it into a date format it recognises - this is mm/dd/yyyy

Just use a line of code after the find / replace like

selection.numberformat = "dd/mm/yyyy"

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Kevsim]

xlbo, Thanks for the info. I have tried what you suggested as in my explanation. I cannot understand, as I record the macro all works well, when I run the macro, the Month and Day fields reverse.
I also tried this in another way, by typing the following in a new worksheet, 2.1.04 (cell1) 4.1.04 (cell2) 8.1.04 (cell3) 20.1.04 (cell4) in the same column. I then tried to convert them by recording a macro, all worked OK, I then run the macro, the fields reverse. I also started by formatting the total column as text, still a problem.
Below is my recorded code, which worked while recording but failed when run as a macro. I would appreciate some advice.

Columns("F:F&quot.Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.NumberFormat = "d-mmm-yy"
End Sub

kevsim

 

[xlbo]

Ok - this one is quite hard to explain but basically, VBA luuuurrrves mm/dd/yyyy - it just can't see past it. When you perform the actions manually, vba is not involved and excel uses your regional settings to determine what "kind" of date you are trying to enter. When you play the macro through VBA, it is doing the same thing but because VBA is in control, it tries to set ALL dates it sees to mm/dd/yyyy

You will notice that if you had a date like
31.01.2004, it won't actually be converted to a date by this process as VBA doesn't accept 31/01/04 as a date because it is thinking mm/dd/yyyy

You need to convert to the date SERIAL number to make this work - the following should do the trick - select all the cells you need to convert and run it:

Sub DateCorrector()
Application.ScreenUpdating = False
For Each c In Selection
    c.Value = DateValue(Replace(c.Text, ".", "/"))
Next c
End Sub

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Kevsim]

xlbo, Thanks for the info, works great.
kevsim

 

[TonyJollans]

Hi Kevsim,

I think a star is in order for Geoff and his explanation of date processing, which is one the hardest things to fully understand because every layer of code seems to treat them slightly differently.

You have your solution; I will give Geoff a star on your behalf.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.