Format a column automatically?

  • Thread starter Thread starter magic kat
  • Start date Start date
M

magic kat

I need to be able to retrieve a text file and have it automatically display
a date column in a date format, rather than the value 39288.3743055556 or
whatever value comes across. What happens is we retrieve the file, and even
though the value is correct, the display isn't. So when the user re-saves
the file as a csv file, the value is lost. What actually gets saved is
..3744305556 which is useless, I can't even convert that back to a date
value.

So is there a way to automatically have a column retrieved to display
correctly? Right now we always have to reformat that column so the date
07/25/2007 8:59:00 AM is displayed correctly, then when saved as a csv file,
it is saved with the correct value. I would like this to be automatic.
Thanks for any help or ideas!
 
#1 You may "import" (Data>Get External Data>Import Text File) and be
specific about the data types in the columns you import. This is
usually more labor-intensive than #3 below.

How are you "retrieving" this text file currently?

#2 You could use a macro to quickly apply the datetime formatting for
you, but it's almost as easy to do it manually (#3 below)

#3 You could just click,keystroke,click,click,click, and manually
apply the formatting to the column after you have retrieved your data.

[click: column heading]
[keystroke: Ctrl-1 -- format cells]
[click: Category=Date]
[click: Type=m/d/yyyy h:mm am/pm
[click: OK]


Brian Herbert Withun
 
Depending on how you retrieve the file, most import utilities provide for
individual formatting of each column.........but if not,

Maybe this macro.......

Private Sub Worksheet_Activate()
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yy"
Range("A1").Select
End Sub

Vaya con Dios,
Chuck, CABGx3
 
Back
Top