Date Formatting Problems in VBA

[Colin Vicary]

Hello All

Firstly, apologies for not wrapping the code below. The link in the forum rules to show me how to do it appears to be broken

I'm struggling to change the format of some dates that are imported as text.

In a previous request ...

http://www.excelforum.com//showthrea...4&goto=newpost

I was shown a way to convert dates correctly.

This solution works perfectly when carried out manually, but doesn't work if a record a macro and then replay it - only some of the dates are converted.

Here is a small sample of the dates I'm working with...

04/09/2008
05/10/2007
08/10/2007
14/09/2007
15/09/2007
17/09/2007
17/09/2007
17/09/2007
19/03/2008
20/09/2007
21/09/2007
21/09/2007
25/09/2008
27/09/2007
28/09/2007
28/09/2007

And here's the recorded macro, deliberately left exactly as is

Please Login or Register  to view this content.

Can anyone help please?

Many thanks

Colin

[MattShoreson]

works fine for me.

What are your local date settings


why dont you just use...

Range("A1:A16").NumberFormat = "mm/dd/yyyy"

[Colin Vicary]

Thanks for that Matt

Doesn't work fine for me, in this particular case only the first three cells changed correctly.

I don't know what my local date setting are, but that's possibly fairly academic as I'm specifying the date format.

Changing the format of the cells to "dd/mm/yyyy" does not change the formatting of these cells from the current "text" format.

Colin

[MattShoreson]

so the original cells are formatted as text, not date?

[Colin Vicary]

Yes, that's correct.

[Colin Vicary]

Matt

I get this message...

Compile Error:

Wrong number of arguments or invalid property assignment

Colin

[MattShoreson]

if you step throught the code, at which line does it error?

[Colin Vicary]

Hi

It errors here, highlighting the objcell in brackets.

objcell = Format(CDate(objcell), "mm/dd/yyyy")

Colin

[MattShoreson]

In the immediate window

type ?objcell.address
Which cell does it refer to?

if you then type ?objcell.value
what value does it return?

[Colin Vicary]

It didn't actually get that far. When I press F8 to start the step through, it reported the compile error and wouldn't go any further.

Colin

[MattShoreson]

did you put the code in the worksheet module or a 'normal' module?

[Colin Vicary]

I pasted it over the top of the existing "macro3". I find that the best method to avoid problems

[Colin Vicary]

Matt in reply to your suggestion as follows:-

try altering the problem line to a msgbox line. Does it display the date?
If so add in the cdate... cdate(objcell.value)

probably best to explicity name the sheet just in case. (use your sheet name)

Please Login or Register  to view this content.

The messageboxes display the dates correctly.

I tried changing

Please Login or Register  to view this content.

to specify a particular cell, at that point the error changed to "too many paramaters" and highlighted FORMAT.

Hope this helps

Colin

[MattShoreson]

next step...

try changing the msgbox objcell.value to msgbox cdate(objcell.value)

does it still display the date?

[Colin Vicary]

It does, yes

[MattShoreson]

try replacing the msgbox line with...

objcell = cdate(objcell.value)

[Colin Vicary]

Hi Matt

That worked fine, and ran through all the cells converting them to date format. However, it has converted them in mm/dd/yyyy even though your format command stipulated dd/mm/yyyy

Thanks for your patience with this so far!

Colin

[MattShoreson]

so they are in the format mm/dd/yyyy?

[Colin Vicary]

That's right.

Originally eg 04/09/2007(formatted as text), now 09/04/2007 (formatted as date)

[MattShoreson]

I thought that's you needed.

from the original piece of code the number format was mm/dd/yyyy.

Do you need dd/mm/yyyy?

[Colin Vicary]

Yes please.

It thought by changing your orginal code to the correct format, that would do the trick.

Colin

[MattShoreson]

Instead of the for next loop use objcell.numberformat = "dd/mm/yyyy"

[Colin Vicary]

Hi Matt

I'm afraid that didn't change the format of the cells.

Colin