excel vba convert
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Convert Text Date with 2-Digit Year in to real date

Excel VBA - Convert Text Date with 2-Digit Year in to real date

resolvedResolved · Urgent Priority · Version 2010

Bin has attended:
Excel VBA Intro Intermediate course
Access Introduction course
Access Intermediate course
Access Advanced course
Excel VBA Advanced course

Excel VBA - Convert Text Date with 2-Digit Year in to real date

Hi
I have some CSV raw data. After i used text to column, in the date column, there are one or two converted in text format.
Excel shows it as Text Date with 2 Digit Year , looks like "dd/mm/yy".
Is there any macro that can convert these ones into date format like "dd/mm/yyyy".
I have tried numberformat="dd/mm/yyyy". but it's not working. I have also tried below codes. however VBA editer says Typing mismatch. Please let me know if there is any other ways
Many thanks
Bin

Dim i As Variant
For Each i In Range("e2:e10000")
i.Value = DateValue(i.Text)
Next


RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Have also tried some other ways including:
- Copy a blank cell over and them paste special value + add to the date cell.
- TextToColumn again to the date column
Non of above works with VBA. it only works when do them mannually.
Excel auto error correction for "Cells containing years represented as 2 digits" is ticked
Please let me know if there is anyther way.
Thanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Bin,

The code

for Each i In Range("e2:e10000")
i.Value = DateValue(i.Text)
Next
Range("e2:e10000").numberformat="dd/mm/yyyy"

will turn text dates to number dates and give you 4 digits years. If you get the error number 13 Type mismatch then it is because i or the dates cannot be text dates or you have some hidden characters you need to clean from the range.

Please send me a copy of your workbook I need to see the dates before I can help you.

Send the workbook to info@stl-training.co.uk

Please add my name in the subject.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Jens,

I have tried your code but debug has happend again @ "i.Value = DateValue(i.Text)". It might have been as you said some hidden characters that were needed to clean. Please see my email.

Also, when i have done the TexttoColumn, the date column some times showed mm/dd/yyyy while sometimes showed dd/mm/yyyy. Is there anyway to fix it as dd/mm/yyyy? for full details please see my emails.

Thanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Bin,

I can first get the email tomorrow, but I think I know the problem. Some of your dates in column E are text date and some are number date. Try this:

for Each i In Range("e2:e10000")
if worksheetfunction.istext(i.value) then

i.numberformat="dd/mm/yyyy"

else
i.value=month(i.value) & "/" & day(i.value)& "/" & year(i.value)
end if

Next

Please let me know if it is working.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Jens,
THanks very much for your prompt reply.
Yes as you said above, in column e there are both TextDate and number date. so the data is mixed.
I did try your code, but the Textdate is still there. it was not converting. I don't know why.
Please do have a look my file and email and let me know if possiblly anyother way can do.
Many thanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Bin,

Your dates in the sheet is a combination of text, UK dates, US dates and number dates.

There is no pattern some text dates are US dates some are UK dates. Some number dates are US dates and some UK dates.

It is not possible to get it right with a macro if there is no pattern in the data.

If I can help you I need the csv file. May be something went wrong when you converted it to xlsm.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Jens, Appreciated your reply.
The csv data is saved on the file i sent to you on the Sheet1 page.
I did TextToDate to the column A on the page.
The code in the moudle1 does include the TextToDate function.
Please let me know you have any way to deal with it
Many thanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Bin,

I can see the data, but this is not csv it is converted to Excel. When data are converted Excel try to help (in your case not a good help) and if Excel recognise a date Excel date format the date. If Excel cannot recognise it as date, Excel will not change the formatting. In your case. Your computer have in the settings UK dates. When you convert a US date 12/28/2014 (12th December 2014) Excel cannot recognise the date as a date, because Excel will see 28 as the number of the month and will therefore keep the text formatting. If you have a US date 6/4/2014 (4th June), Excel can recognise it as a date and number date format the date, but it will be wrong because you will get the date 6th of April.

This is a large problem for many companies.

If you can send me the source file (database file, text file, csv file) I can see if you can do something when you are converting the data.

If not- I cannot help you because it cannot be done in the Excel file you sent me.

Excel cannot understand that sometimes 6/4/2014 is 4th June and sometimes 6th April.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Jens,
Thanks for your explaination in above.
I have forward you the file to info@ inbox.
Please let me know what you think
Many thanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Bin,

No I am sorry.

I cannot help you.

It is your system. You need to talk to your IT department this cannot be done in Excel. It must be done before the data reach Excel. You must get the data as a text file not Excel.

Normal txt files are normally easier to handle than csv. If you google our problem you will find out that many have your problem and there is different solutions but I have tried all without luck.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Ok thanks Jens,
What format should it be? do you have an example of the name?
Thanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Bin,

I have good experiences with txt files, but I do not know your system.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Jens,
I have got the text file. Can you please also have a look and see if you can solve the TextDate issue?
I have emailed it to you.
Please let me know
Many thanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Bin,

Yes I have got the text file and yes it seems to be the answer to your problem. If you in Excel under the data tab in the get external data group click from text. This will open the import wizard. in step 1 make sure that delimited is selected. in step 2 select space delimited and click finish.

I do not know if this will mess up any other data but you will get the dates correct.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Jens,
it does mass up other rows.
i simply select all from the text file and then copy and past to the excel file?
then i get the issue again.
Is there any other way can deal with it?
because i notice that only the textdates are in british date style. however the rest of them are all in US format.
Can i do a if funcation and apply mid() formula. to the textdate cells extract mm part and to the rest extract dd part?
Don't know if it would work
Let me know
THanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Jens,
Do you mean that i will need to change the system's (where i export the raw data) default setting of the date to the british format? you mean the raw data has usa date format?
Please reply me
Thanks
Bin

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Bin,
I have tested my suggestion importing the data from a text (txt) to Excel using Get External Data from text. I copied the dates and pasted the dates in the workbook you sent me. Everything was fine. All dates were UK number formatted. I do not know what you are doing wrong. Always in Excel start from a text file (txt) never from CSV. Use the import wizard or create a macro doing it. We did it on the advanced vba course.

It doesn’t make sense for me to start using the mid function, but if you want taking that road, you must use a combination of Mid, Left, Right and Search or Find functions.
You can find how if you click this link below:

http://office.microsoft.com/en-gb/excel-help/split-text-among-columns-by-using-functions-HA102749552.aspx

Sorry I cannot help you more with this question. You have got all the tools and macros from me you need and I do not know why it is not working when you are trying.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - Convert Text Date with 2-Digit Year in to real d

Hi Jens, I have solved the issue myself. The answer lays in the TexttoColumn process. You need to highlight the date column and edit the format. But thanks for your reply.

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel+and+powerpoint+courses+uk and other training courses.

Excel tip:

Go to source of a cell

The default setting in Excel is when you double click in a cell it actives the formula in the cell. If you have created a link and want to directly go to that link (say if on another sheet, click on Tools -options and take off the tick for eidt it directly in cell

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.