Macro not working properly for conversion of date format

**abraham30** · 04-13-2014, 01:27 AM

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** · 04-13-2014, 03:29 AM

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** · 04-13-2014, 03:37 AM

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** · 04-13-2014, 03:40 AM

There, I think I got the bugs out:

Please Login or Register  to view this content.

**abraham30** · 04-13-2014, 02:00 PM

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** · 04-14-2014, 10:46 AM

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

Macro not working properly for conversion of date format

LinkBack

Thread Tools

Rate This Thread

Display

Macro not working properly for conversion of date format

Re: Macro not working properly for conversion of date format

Re: Macro not working properly for conversion of date format

Re: Macro not working properly for conversion of date format

Re: Macro not working properly for conversion of date format

Re: Macro not working properly for conversion of date format

Thread Information

Users Browsing this Thread

Similar Threads

Macro isn't working properly.

Macro Not Working Properly

Date Sort in Pivot Table - Not working properly

Conversion de date d'un format personnalisé à un format de date standard

[SOLVED] Macro Not Working Properly

Bookmarks

Bookmarks

Posting Permissions