Hi,
Further to Sam's solution, here is the complete code
Sub converToDate()
Dim sht As Worksheet
Dim rng As Range
Dim r1 As Integer, r2 As Integer
For Each sht In ThisWorkbook.Sheets
Set rng = sht.Range("A1")
Do While UCase(Trim(rng.Value)) <> "DATE"
Set rng = rng.Offset(1)
Loop
Set rng = rng.Offset(1)
r1 = rng.Row
r2 = rng.End(xlDown).Offset(-1).Row
sht.Range("A" & r1 & ":A" & r2).TextToColumns Destination:=rng,
DataType:=1, TextQualifier:=1, FieldInfo:=Array(1, 4),
TrailingMinusNumbers:=True
Next sht
End Sub
Pls find attached excel for ref.
Press Ctrl + q to run the code.
Regards,
Swapnil.
On Thu, Oct 6, 2011 at 11:06 AM, Sam Mathai Chacko <samde...@gmail.com>wrote:
> Well, actually, use
>
> Range("A3:A6").TextToColumns Destination:=Range("A3"), DataType:=1,
> TextQualifier:=1, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
>
> Sorry for posting without testing the previous one.
>
>
> Regards,
>
> Sam Mathai Chacko (GL)
>
>
> On Thu, Oct 6, 2011 at 10:57 AM, Sam Mathai Chacko
> <samde...@gmail.com>wrote:
>
>> Range("A3:A6").TextToColumns Range("A3"), 0, 1, False, True, False, False,
>> False, False, Array(1, 4), True
>>
>> Regards,
>>
>> Sam
>>
>>
>> On Thu, Oct 6, 2011 at 10:56 AM, B.N.Chethan kumar <
>> chetankumar1...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> As the data volume is high... i will have 100 to 200 sheets in a
>>> month..hence take more time....
>>>
>>> Can we write any macro to convert for all sheet in the work book.
>>>
>>> Thanks Asa Rossoff...i will try below option.
>>>
>>> Regards
>>> Chethan Kumar BN
>>>
>>> On Thu, Oct 6, 2011 at 10:48 AM, Asa Rossoff <a...@lovetour.info>wrote:
>>>
>>>> Hello B.N.Chethan,****
>>>>
>>>> ** **
>>>>
>>>> Here are two methods courtesy of
>>>> http://www.ozgrid.com/Excel/convert-true-time-date.htm****
>>>>
>>>> ** **
>>>>
>>>> 1. Option 1: Copy an empty cell, then highlight the cells (or entire
>>>> column(s)) containing your textual date/times, then Paste Special>Add. The
>>>> add operation forces Excel to convert the date/time.****
>>>>
>>>> ** **
>>>>
>>>> 2. Option 2: use a formula in another cell that does a neutral math
>>>> operation like *=A3+0* or *=A3*1* or *=--A3*. You could if desired
>>>> copy those cells to another location using Paste Special>Values.****
>>>>
>>>> ** **
>>>>
>>>> I'll just add that if the data is being imported from a text file, make
>>>> sure you highlight the column containing date/times during import and
>>>> specify the correct date format for that column; it may avoid the whole
>>>> problem.****
>>>>
>>>> ** **
>>>>
>>>> Asa****
>>>>
>>>> ** **
>>>>
>>>> *From:* excel-macros@googlegroups.com [mailto:
>>>> excel-macros@googlegroups.com] *On Behalf Of *B.N.Chethan kumar
>>>> *Sent:* Wednesday, October 05, 2011 9:50 PM
>>>> *To:* excel-macros@googlegroups.com
>>>> *Subject:* $$Excel-Macros$$ Need a help -----very urgent****
>>>>
>>>> ** **
>>>>
>>>> Hi All,****
>>>>
>>>> ****
>>>>
>>>> I have attached file where i get some data in text format. I need
>>>> convert to date and time format.****
>>>>
>>>> ****
>>>>
>>>> Currently using text to coloumn method which is manually pocess. As
>>>> sheet count is high in a workbook, these is very painful work to do in
>>>> month
>>>> end.****
>>>>
>>>> ****
>>>>
>>>> Kindly request your help to very painful work.
>>>>
>>>> -- ****
>>>>
>>>> Regards
>>>> B.N Chetan kumar****
>>>>
>>>> --
>>>>
>>>> ----------------------------------------------------------------------------------
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel****
>>>>
>>>> --
>>>>
>>>> ----------------------------------------------------------------------------------
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel
>>>>
>>>
>>>
>>>
>>> --
>>> B.N Chetan kumar
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> Sam Mathai Chacko
>>
>
>
>
> --
> Sam Mathai Chacko
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Sample data.xlsm
Description: Binary data
