HI Swapnil,
Thnxs for the code...along with date..i also need to convert time from
text....please refer coloum O to AA in excel.
Kindly help me with the code...please....
On Thu, Oct 6, 2011 at 11:36 AM, Swapnil Palande <
palande.swapni...@gmail.com> wrote:
> 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
>
--
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
