I have written a Visual Basic program (using Office 2000) that:

1. Imports a space-delimited text file that contains 5 columns for dat
data (Year, Month, Day, Hour, Minute) into Sheet1

2. Concatenates the five fields in a string formula for the firs
record as 'Month/Day/Year Hour:Minute', placing it in another colum
on Sheet1

3. Copies this formula for all records into the same column

4. Copies the column range to Sheet2 by value

The IsDate function returns True for the dates on both Sheet1 an
Sheet2.

THE PROBLEM: The appearance of the dates is unexpected since they ar
at the left of the cell, in the form '1/1/2002 0:0', but jump to th
right, and to the form '1/1/1002 00:00', if the cell is edited.
think it will confuse my client as it does me.

Already attempted fixes: I have tried formatting the ranges that wil
contain the dates on Sheets 1&2 with .NumberFormat("m/d/yyyy h:mm").
Neither doing the formatting before the entry/copy nor after th
entry/copy works to give me a date
at the right of the cell.

Is it the string formula? How do I avoid using it? If that's not it
can ANYONE please give me a clue to a fix

--
Message posted from http://www.ExcelForum.com