convert date and time with milisecon in excel

[guddu_12]

Deal All,

I am data in txt file and i am doing import in excel to do reporting. while importing excel convert the date column by it own to the nearest value. eg i have data like this 17/07/2012 14:08:13.923 , once import done excel convert it to 17/07/2012 14:08:14. how can i retain the actual values as milisecon is very important and is being used in many calculation.

Please help

Rajesh

 

[pgc01]

Hi Rajesh
Welcome to the board

2 options

1 - check if the cell format is the correct one, meaning if it includes displaying the miliseconds. It may be that the value is ok and you're just not displaying it (".000" in the format)

2 - if the value is not correct then the problem is in the import process. In that case import that field as text (not as datetime) so that you get all the value as text into excel. You can then convert it to excel datetime.

 

[guddu_12]

Hi PGC,

The date format for the cell is dd/mm/yyyy hh:mm and i can't see any format listed there to accept valuea as dd/mm/yyyy hh:mm:ss.000. Does excel have dd/mm/yyyy hh:mm:ss.000 this format available.

When i am importing from the text file it convert the date automatically and make round of milisecod in the date. if i import the text data then how can i convert in dd/mm/yyyy hh:mm:ss.000. is there any formula available ?

Rajesh

 

[pgc01]

The date format for the cell is dd/mm/yyyy hh:mm and i can't see any format listed there to accept valuea as dd/mm/yyyy hh:mm:ss.000. Does excel have dd/mm/yyyy hh:mm:ss.000 this format available.

The formats listed are just examples. You can define any format that respects the syntax.

Right click on the cell

Format Cells->Number->Custom

In the "Type:" text box write

dd/mm/yyyy hh:mm:ss.000

and press enter. If you have a value with miliseconds they will be displayed.

 

[pgc01]

... if i import the text data then how can i convert in dd/mm/yyyy hh:mm:ss.000. is there any formula available ?

You can use a formula like

=A1+0

and format the cell.

You can also convert the values in situ:

- write the number 0 (zero) in a cell
- copy
- select the cells with the dates as textvalues
- Paste Special->Add

... and format the cells.