Help with millisecond truncation in date formatting.

[mark_ca]

I am importing a date from an external file and extracting numbers from the string for calculation purposes, however Excel seems to have a different agenda for me. I have a date value of "12/27/2015 02:43:20.775 PM" but the cell value becomes "12/27/2015 02:43:21 PM" when I try and call the value from the cell. I have attached a screenshot below. I need to retain the information provided by the milliseconds.

I have tried both workarounds suggested by https://support.microsoft.com/en-us/kb/875526 without success. Any suggestions would be appreciated.

 

[MarcelBeug]

Don't see a problem, except the formula bar shows a value rounded to seconds.
However, the cell value displays the value in milliseconds, so that should be fine.
You can verify by subtracting the value in C2 from 12/27/2015 2:43:21 PM which returns a tiny value equivalent to 225 milliseconds.

 

[mark_ca]

Don't see a problem, except the formula bar shows a value rounded to seconds.

That's the whole problem. If I use a macro to pull the value from cell C2, the string in the macro becomes what is shown in the formula bar, ie: "12/27/2015 2:43:21 PM", and I lose all information regarding the 0.775 of a second which I need to retain.

 

[jorismoerings]

Hi,

That's probably caused by the definition of the variable you're using to pull the cellvalue into. The VBA Date definition doesn't support miliseconds but there is a work a round.

Take a look ath this: VBA Tips & Tricks: Excel VBA TimeStamp – Milliseconds using Excel VBA

Hope it helps

 

[MarcelBeug]

That's the whole problem. If I use a macro to pull the value from cell C2, the string in the macro becomes what is shown in the formula bar, ie: "12/27/2015 2:43:21 PM", and I lose all information regarding the 0.775 of a second which I need to retain.

You didn't mention macro / VBA...