98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
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
Resolved · Urgent Priority · Version 2010
Wed 20 Aug 2014, 15:42Delegate Bin said...
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
Wed 20 Aug 2014, 17:13Delegate Bin said...
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
Wed 20 Aug 2014, 20:44Trainer Jens said...
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
Wed 20 Aug 2014, 21:25Delegate Bin said...
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
Wed 20 Aug 2014, 21:47Trainer Jens said...
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
Wed 20 Aug 2014, 22:01Delegate Bin said...
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
Thu 21 Aug 2014, 09:31Trainer Jens said...
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
Thu 21 Aug 2014, 09:51Delegate Bin said...
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
Thu 21 Aug 2014, 10:42Trainer Jens said...
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
Thu 21 Aug 2014, 11:01Delegate Bin said...
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
Thu 21 Aug 2014, 11:18Trainer Jens said...
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
Thu 21 Aug 2014, 11:30Delegate Bin said...
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
Thu 21 Aug 2014, 11:41Trainer Jens said...
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
Thu 21 Aug 2014, 11:57Delegate Bin said...
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
Thu 21 Aug 2014, 12:40Trainer Jens said...
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
Thu 21 Aug 2014, 14:09Delegate Bin said...
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
Fri 22 Aug 2014, 09:28Delegate Bin said...
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
Fri 22 Aug 2014, 13:31Trainer Jens said...
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
Training information:
|
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Go to source of a cellThe 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 |
