need Formula Please

[crimmelcp]

Excel 2007
Cell A contains the following data
HALCHAK JR, JOHN 10/31/2010 10/28/2010 Williams 9

I need a formula for cell E and F
Cell E needs to extract the first date from Cell A (in this example it would be 10/31/2010)

Cell F needs to extract the second date from Cell A (in this example it would be 10/28/2010)

Thanks
Charlie Crimmel

 

[VoG]

Try

E1 =MID(A1,FIND("/",A1)-2,10)

F1 =MID(A1,FIND("/",A1)+9,10)

 

[crimmelcp]

It works if the date is always mm/dd/yyyy
but if the date is m/dd/yyyy of mm/d/yyyy

How can I force the date to be mm/dd/yyyy

Excel 2007 does not seem to have a custom date

 

[crimmelcp]

more test data

<TABLE style="WIDTH: 269pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=358><COLGROUP><COL style="WIDTH: 269pt; mso-width-source: userset; mso-width-alt: 13092" width=358><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 269pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=358>EVANS, MITCHELL 12/19/2010 12/13/2010 Williams 5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>EVANS, MITCHELL 1/16/2011 1/14/2011 Williams 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>EVANS, MITCHELL 12/5/2010 12/2/2010 Williams 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>EVANS, MITCHELL 1/2/2011 1/2/2011 Williams 0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>EVANS, MITCHELL 1/2/2011 12/31/2010 Williams 8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>EVANS, MITCHELL 1/2/2011 12/30/2010 Williams 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>EVANS, MITCHELL 1/2/2011 12/29/2010 Williams 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>EVANS, MITCHELL 1/9/2011 1/5/2011 Williams 2</TD></TR></TBODY></TABLE>

 

[Sandeep Warrier]

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">EVANS, MITCHELL 12/19/2010 12/13/2010 Williams 5</td><td style=";">12/19/2010</td><td style=";">12/13/2010</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">EVANS, MITCHELL 1/16/2011 1/14/2011 Williams 1</td><td style=";">1/16/2011</td><td style=";">1/14/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">EVANS, MITCHELL 12/5/2010 12/2/2010 Williams 2</td><td style=";">12/5/2010</td><td style=";">12/2/2010</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">EVANS, MITCHELL 1/2/2011 1/2/2011 Williams 0</td><td style=";">1/2/2011</td><td style=";">1/2/2011</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">EVANS, MITCHELL 1/2/2011 12/31/2010 Williams 8</td><td style=";">1/2/2011</td><td style=";">12/31/2010</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">EVANS, MITCHELL 1/2/2011 12/30/2010 Williams 4</td><td style=";">1/2/2011</td><td style=";">12/30/2010</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">EVANS, MITCHELL 1/2/2011 12/29/2010 Williams 3</td><td style=";">1/2/2011</td><td style=";">12/29/2010</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">EVANS, MITCHELL 1/9/2011 1/5/2011 Williams 2</td><td style=";">1/9/2011</td><td style=";">1/5/2011</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">A1,FIND(<font color="Green">"/",A1</font>)-2,FIND(<font color="Green">" ",A1,FIND(<font color="Purple">"/",A1</font>)</font>)-FIND(<font color="Green">"/",A1</font>)+2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=MID(<font color="Blue">A1,FIND(<font color="Red">" ",A1,FIND(<font color="Green">"/",A1</font>)</font>)+1,FIND(<font color="Red">" ",A1,FIND(<font color="Green">" ",A1,FIND(<font color="Purple">"/",A1</font>)</font>)+1</font>)-FIND(<font color="Red">" ",A1,FIND(<font color="Green">"/",A1</font>)</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />