Macro not working properly for conversion of date format

[abraham30]

Hi,

In the attached sheet, one macro is developed which is not working properly. Can anyone help me.

My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)

If incomplete date is there, then hyphen should place the gap i.e.

For date like APR-2014------> Output should be 2014/04/--
For date like 1998 -----> Output should be 1998/--/--

The below macro works only for row number 11, 15 and 16. can anyone have a look on it

Please Login or Register  to view this content.

[daffodil11]

Well, all of the cells are in General Format even if the way the data isn't. First, you should loop through with .Value = .Value, which will coerce the values into the formats they were entered as.

Please Login or Register  to view this content.

[daffodil11]

Also, you can run a quick check on the data types with

Please Login or Register  to view this content.

which prints out in the immediate window:

General
mmm-yy
d-mmm-yy
d-mmm-yy
d-mmm-yy
d-mmm-yy
d-mmm-yy
d-mmm-yy
d-mmm-yy
dd-mmm-yyyy
d-mmm-yy
d-mmm-yy
d-mmm-yy
dd-mmm-yyyy
dd-mmm-yyyy
d-mmm-yy

There appears to be some variation to account for.

[daffodil11]

There, I think I got the bugs out:

Please Login or Register  to view this content.

[abraham30]

Thanks Daffodil for looking into my issue.

But the issue is still there. The macro suggested by you is not working. All date ( including partial date) converts into number format.
Is there any alternative way..

[daffodil11]

It worked fine for me. Here is an attachment with the working code.