Thanks to everybody for their help...this is what I ended up doing in
the macro.
I guess a combo solution from everybody's input.

Range("J5").Select
Selection.FormulaR1C1 = "=MID(RC[-5],1,9)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=INT(RC[-1])"
Range("J5:K5").Select
Selection.AutoFill Destination:=Range("J5:K" & lastrow),
Type:=xlFillDefault
Range("K5:K" & lastrow).Select
Selection.Copy
Range("E5:E" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _ :=False, Transpose:=False

It seems to work just fine for the moment.
Now I'm having to think about the following

The current date is 1/13/2006 what if the date is 10/10/2006 1:30
Then the MID solution using 9 characters won't entirely work.


--
wayliff
------------------------------------------------------------------------
wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=501062