Change the column A format to Date

[aman1234]

Hi Guys


I have an Excelsheet and when the data gets added in that using vba code then the first Column stores number like 20140626 but i want to convert in to date like 26/06/2014. Can this be done?

When I try to change the format of the column to Date then it displays ######## in that column.

Any help would be much appreciated.

Thanks

[romperstomper]

20140624 is not a date value - it's a large integer (too large for Excel's dates, which is why you see ####). You can use a formula in another column like:
=TEXT(A1,"0000-00-00")+0
to convert to a true date.

[Fotis1991]

Try using Cdate.

https://www.udemy.com/blog/vba-cdate/

[romperstomper]

CDate won't work with a 'date' in that format. You'd need to use Format as well.

[Fotis1991]

Thanks for let me know that, Roy. Still learning!!

[pareshj]

Hi aman1234,

Try this formula and hope this will help you:

Please Login or Register  to view this content.

Where A2 = 20140626


Regards,
Paresh J

[romperstomper]

That will return text, not a true date. The version I posted will return a true date value, the column just needs formatting.

[pareshj]

Hi romperstomper,

Even this will return date, but will need formatting:

Please Login or Register  to view this content.

But, I have checked the below formula in Excel 2010.

Please Login or Register  to view this content.

It shows me as Date format.


Regards,
Paresh J

[romperstomper]

It shows in date format but it's formatted text, not a date. That may not be a problem for the OP but I wanted to point it out.

[pareshj]

Hi romperstomper,

Okk. THanksss for sharing this.

Regards,
Paresh J

[aman1234]

Thanks romperstomper. this works fine but Can the same thing be done using vba so that column A will be automatically be replaced with Date?

[romperstomper]

You may be able to add it to your code that populates the sheet initially. What's that?

[aman1234]

Romperstomper,

The following code copies data from Access to Excel but as ScanDate has number data type so when it gets copied to Excel then column B stores Scandate in number like 20140626 but I want the code after the following code to automatically change it into 26/06/2014.

I hope it makes sense.

Please Login or Register  to view this content.

Thanks

[romperstomper]

Try changing the SQL:

Please Login or Register  to view this content.

[aman1234]

it gives me syntex error:

Please Login or Register  to view this content.

[aman1234]

also, Expected End of statement compile error at followinf line:

CDate(Format(Scandate,"0000-00-00"))

[pareshj]

Hi aman1234,

Try to use,

Please Login or Register  to view this content.

[romperstomper]

Sorry - forgot to double the quotes:

Please Login or Register  to view this content.

[aman1234]

Thanks a million romperstomper. Its working perfectly..

[romperstomper]

Glad to help. Please remember to mark Solved.