converting dates to general type of data

**Vogelmann** · 10-17-2012, 09:26 AM

Is it possible to convert cells that contain dates to general type without changing their content?

For example, I`ve got 6/05/2009 in cell J7 (date - type of data) and I still want to have 6/05/2009 in cell J7, but as a general type of data. Is it possible to do it with using a vba code? I need to do that as a part of a bigger operation (copying cells from one workbook to another) and I need to do it in VBA.

you can see the same question and suggestions from other users in this topic:
http://www.mrexcel.com/forum/excel-q...al-format.html

Thanks in advance for your help!

Karol

**Tsjallie** · 10-17-2012, 02:15 PM

Karol,
you can just move the worksheet cell (which can be date format) to variable (dim as string or variant) and do whatever you like to do with it like it were a string.
You can also dim the variable as single, double or long. Then can handle it as a number.

**Vogelmann** · 10-18-2012, 03:20 AM

Tsjallie I don`t get what you exactly mean, sorry for my lack of experience

With some help from outside I obtained the following code:

Please Login or Register  to view this content.

my data range starts from the row number 7 (e.g. K7). I must not change cells that are in rows 1:6. The number of rows is variable - it might be 50 or 400 rows with data, but it always starts from row 7.

The question now is: can the date be changed into DD/MM/YYYY format? While converting to text from date using the code above the order of numbers has been changed - now it`s MM/DD/YYYY but I still need DD/MM/YYYY, even if it is a text format. How to deal with it?

**Tsjallie** · 10-18-2012, 09:00 AM

Karol,
you can try to alter Array(1,2) into Array(1,4) thus specifying a date in DMY-format or - if you like - Array(1,1) to specify general format.

Possible datatypes with Fieldinfo are:
1 xlGeneralFormat. General
2 xlTextFormat. Text
3 xlMDYFormat. MDY Date
4 xlDMYFormat. DMY Date
5 xlYMDFormat. YMD Date
6 xlMYDFormat. MYD Date
7 xlDYMFormat. DYM Date
8 xlYDMFormat. YDM Date
10 xlEMDFormat. EMD Date (Taiwanese era dates are used)
9 xlSkipColumn. Skip Column

I found this list in https://www.excelforum.com/showthread.php?t=522433 (post #5)

**Vogelmann** · 10-24-2012, 08:29 AM

I`refreshing the topic, the solution is still needed
previously I applied the following code:

Please Login or Register  to view this content.

but I realized that it changes the order of numbers in a date, e.g. when you originally have 4/05/2008 in a date format, after applying the code it changes to 5/04/2008 in a text format. This led to a lot of problems now and I need to fix it as soon as possible - could you please help me with that? It will be appreciated a lot.

**JosephP** · 10-24-2012, 08:41 AM

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

**Vogelmann** · 10-24-2012, 08:49 AM

that`s right Joseph, I posted the same question on another forum; my first post has been already edited and link to the same question on the other forum is included.
Sorry for not mentioning it before.