Need help in Macro where two dates in a cell can be shown in different cell

[Vijaya Kanthi]

Need help in creating a MACRO for separating 2 dates in a cell and show in different cell and the format of the date should be YYYYMMDD

example: [TABLE="width: 833"]
<colgroup><col width="1110" style="width: 833pt; mso-width-source: userset; mso-width-alt: 40594;"> <tbody>[TR]
[TD="width: 1110, bgcolor: transparent"]If A1 = The Flow (15/09/2009 00:00) is prior (01/09/2013 00:00).

In B1 ( I want)
20090915

In C1 ( I want )
2010901

A1 data should not change

can any one help in this????

Regards
K

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Vijaya Kanthi]

Hello,

sorry for the delay

1. In Column G there are 5000 lines ( with different information)
2. In there is no Date in Column G then Column H & I can be empty
3. If there is Date in Column G then it populate column H & I accordingly

kindly advice

Regards,
K

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG01Aug11
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("G1", Range("G" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp = Split(Dn.Value, "(")
[COLOR="Navy"]If[/COLOR] UBound(Sp) > 0 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
        [COLOR="Navy"]If[/COLOR] IsDate(Split(Sp(1), ")")(0)) [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 1).Value = Format(Split(Sp(1), ")")(0), "YYYYMMDD")
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] IsDate(Split(Sp(2), ")")(0)) [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 2).Value = Format(Split(Sp(2), ")")(0), "YYYYMMDD")
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[nikio8]

Can you not use simple excel?
FORMAT MID Find"(" , YYYYMMDD
FORMAT MID Find"(",starting from first "(", even use substitute to replace second ( with something....

Cancat result

 

[Vijaya Kanthi]

Hello Mick....

thanks a lot for the help... this is working perfectly now!!!!!... that was a great help

Regards,
K

 

[Vijaya Kanthi]

hello niki...

I tried that but the information what I am looking for is not the output... sorry....

anyways thanks for he advice

Regard,
K