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]

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG27Jul46
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
Sp = Split(Range("A1"), "(")
Range("B1").Value = Format(Split(Sp(1), ")")(0), "YYYYMMDD")
Range("C1").Value = Format(Split(Sp(2), ")")(0), "YYYYMMDD")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Vijaya Kanthi]

Hello,

Thanks you for your reply...

but it popping up with a messgae: subscript out of range

kindly advice

Regards,
K

 

[MickG]

What have you got in "A1" ????

 

[Vijaya Kanthi]

A1 = [TABLE="width: 984"]
<tbody>[TR]
[TD="width: 984"]Settlement date MSMTD is before the MS EFD.The MSMTD Date on the Flow (18/10/2007 00:00) is prior to that in the M2000 Database (16/10/2017 00:00). [/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Here is the returned values from the code, in "B1 & C1"

[TABLE="width: 462"]
<tbody>[TR]
[TD="width: 410, bgcolor: transparent"]Settlement date MSMTD is before the MS EFD.The MSMTD Date on the Flow (18/10/2007 00:00) is prior to that in the M2000 Database (16/10/2017 00:00).
[/TD]
[TD="width: 121, bgcolor: transparent, align: right"]20071018
[/TD]
[TD="width: 84, bgcolor: transparent, align: right"]20171016
[/TD]
[/TR]
</tbody>[/TABLE]

How are you running this Code ????

If you are Running from a Basic Module, you need your Data sheet to be showing !!!

 

[Vijaya Kanthi]

Hello,

thank for help...this is working now....

however I have multiple columns with similar date... this macro is working for only A1, not for other columns...

can you help me getting it worked I other columns also?????

Note : There are columns with no dates on it there I only text and few have text with numbers in ( )... I have given few examples below..... can u help me creating macro according to this?

Example :
1. Awaiting D0150 with Meter Technical Details. The old_mtd_terminated flag indicates that the D0150 is yet to arrive for role M
[TABLE="width: 819"]
<colgroup><col width="1092" style="width: 819pt; mso-width-source: userset; mso-width-alt: 39936;"> <tbody>[TR]
[TD="width: 1092, bgcolor: transparent"] 2. Ambiguous assets located in the asset registerUnable to uniquely locate asset for 17P3045283
[TABLE="width: 819"]
<colgroup><col width="1092" style="width: 819pt; mso-width-source: userset; mso-width-alt: 39936;"> <tbody>[TR]
[TD="width: 1092, bgcolor: transparent"]3. Settlement date MSMTD earlier than SSC EFD(SCON)Settlement date MSMTD earlier than SSC EFD(SCON)
[TABLE="width: 819"]
<colgroup><col width="1092" style="width: 819pt; mso-width-source: userset; mso-width-alt: 39936;"> <tbody>[TR]
[TD="width: 1092, bgcolor: transparent"]4. TPRs do not match metering systemThe TPRs on the incoming D0045 flow do not match those valid for the SSC Code (8938)

can you help????

Thanks
K

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


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

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

 

[MickG]

Two things:-
What is the range your data is in ??
What are the expected results from this data

 

[Vijaya Kanthi]

Range - A :G
Outcome : 1. Awaiting D0150 with Meter Technical Details. The old_mtd_terminated flag indicates that the D0150 is yet to arrive for role M (((Same information in Column H)))
2. Ambiguous assets located in the asset registerUnable to uniquely locate asset for 17P3045283 (((Same information in Column H)))
3. Settlement date MSMTD earlier than SSC EFD(SCON)Settlement date MSMTD earlier than SSC EFD(SCON) (((Same information in Column GH)))
4.TPRs do not match metering systemThe TPRs on the incoming D0045 flow do not match those valid for the SSC Code (8938) (((Same information in Column H)))
[TABLE="width: 819"]
<colgroup><col width="1092" style="width: 819pt; mso-width-source: userset; mso-width-alt: 39936;"> <tbody>[TR]
[TD="width: 1092, bgcolor: transparent"] 5.Ambiguous assets located in the asset registerUnable to uniquely locate asset for X99X99999 (((Same information in Column H)))
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 819"]
<colgroup><col width="1092" style="width: 819pt; mso-width-source: userset; mso-width-alt: 39936;"> <tbody>[TR]
[TD="width: 1092, bgcolor: transparent"] Settlement date MSMTD is before the MS EFD.The MSMTD Date on the Flow (15/12/2005 00:00) is prior to that in the M2000 Database (29/03/2006 00:00). ((( YYYYDDMM) in H)) (((YYYYDDMM) in I))

ABOVE DATE WILLBE IN COLUMN G

Thank you for the help

Regards,
K

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

 

[MickG]

Unfortunately this is still not clear !!!
Q1. Are the Lines 1 to 5 in column "G".
Q2. If there are no Dates in a particular line, do you want that line repeated in column "H"
Q3. If there are dates in a particular line, you want those dates n Column "H & I"

Please Clarify