HI Swapnil,
Thnxs for the code...along with date..i also need to convert time from
textplease 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,
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"
Se
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 w
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..henc
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 wrote:
>
Hi B.N Chetan kumar,
Try this...
=--SUBSTITUTE(A3," ","-")
Or
=IF(ISERR(--SUBSTITUTE(A3," ","-")),"",--SUBSTITUTE(A3," ","-"))
Hope that helps!
---
Ms.Exl.Learner
---
On Thu, Oct 6, 2011 at 10:20 AM, B.N.Chethan kumar <
chetankumar1...@gmail.com> 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 t
Thanks SAM, for prompt response...currently i using the same option to
convert date and text to columns option for time...
Due to data size...it is taking more time.
Regards
Chethan Kumar BN
On Thu, Oct 6, 2011 at 10:43 AM, Sam Mathai Chacko wrote:
> =DATEVALUE(LEFT(A3,2)&"/"&MID(A3,FIND(" ",A3
=DATEVALUE(LEFT(A3,2)&"/"&MID(A3,FIND(" ",A3)+1,3)&"/"&RIGHT(A3,4))
Regards,
Sam Mathai Chacko (GL)
On Thu, Oct 6, 2011 at 10:20 AM, B.N.Chethan kumar <
chetankumar1...@gmail.com> wrote:
> Hi All,
>
> I have attached file where i get some data in text format. I need convert
> to date and time f
