Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date shows "00:00.0" rather than actual value
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date shows "00:00.0" rather than actual value
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... "Nigel" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date shows "00:00.0" rather than actual value
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date shows "00:00.0" rather than actual value
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". "Nigel" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date shows "00:00.0" rather than actual value
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" wrote: 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". "Nigel" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date shows "00:00.0" rather than actual value
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" wrote: 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" wrote: 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". "Nigel" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date shows "00:00.0" rather than actual value
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.") "Nigel" wrote: 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" wrote: 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" wrote: 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". "Nigel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables show actual name rather then "Column Labels" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Pivot Table showing number "1" instead of actual result | Excel Discussion (Misc queries) | |||
When I type "13" the spreadsheet shows "14." Possibilities? | Excel Discussion (Misc queries) | |||
In Excel a cell formatted "currency" shows "######" help! | Excel Worksheet Functions |