Date shows "00:00.0" rather than actual value

[Nigel]

Hi, I have a CSV file with a date/time column.

A typical value is: "2009-03-01 00:00:00.000"

When I open the CSV with Excel, the cell shows "00:00.0".

How can I show the exact value in the cell without formatting the cell to
date/time format? I have tried putting the value (in the CSV file) in between
a pair of double quotes before opening it in Excel but it doesn't work.

Regards,
Nigel

 

[Sheeloo]

During import specify the field as a TEXT field...

but then you won't be able to use the values as dates unless you convert
them back to dates...

 

[Joel]

You need to use the Text Import Wizard. If the file extension is TXT then
use the menu Data - Import External Data - Import Data, otherwise, you can
use File Open. The 3rd step of the wizard allows you to select the data
type. Select text.

 

[Nigel]

Hi Sheeloo and Joel,

Thanks for the advice.

But there's no importing and exporting involved. The CSV file will stay in
CSV format, and I'm using Excel to open the CSV file that's all.

I'm looking at how I can manipulate the data in the CSV file so that upon
opening the file using Excel, the value will be shown as it is without
becoming "00:00.0".

 

[Joel]

When you open an application using a window explorer by double clicking it is
the same as if you open the application and go to File menu and open the file.

So even if you want to view the file you can still open can import the file
in Excel. when you are already open the file in excel, importing is just
another way of viewing the file.

 

[Nigel]

Hi Joel,

Thanks for the reply.

I think it will be clearer if I describe the scenario.

The CSV file is generated by a VB.net program and is automatically emailed
to my user. I am the one who wrote the program.

When my user received the email and opened the attached CSV, she sees
"00:00.0" in the date column and claims that there is data error. When I
teach her how to format the column to see the data, she refuses to do so and
demanded that I should do the formatting beforehand before sending the email.

The only thing I can control, is my VB.net program. Eventually what I want
to know is, how should I formulate the date string, such that Excel will
recognise it as a string (and thus show it as it is), rather than show it as
an unformatted date value.

Sorry for the confusion.

 

[Joel]

I was able to repeat your problem. found a solution

from
"2009-03-01 00:00:00.000"

to
March-1-2009 00:00:00

The format statement you should be using in your VB.Net is

MyDate = Now()
StringDate = format(MyDate,"MMMM-dd-yyyy HH:MM:SS.")