Excel time conversion bug(?)

[storm8]

Hi,

I noticed funny behaviour in excel and Im curious if anyone knows why it happens. My locale is CZ, decimal separator is ","

I have a time in this format 2015-05-22 13:39:59.847

if I paste this value into excel, it reads the day correctly and display it as 22.5.2015 13:39:59 (decimal 42146,5694328704)

however, if miliseconds begins with 0, it fails to parse the date and just gets pasted as original text. Can't be formated with DATEVALUE function.

2015-05-22 13:39:59.047 - does not work
2015-05-22 13:39:59.000 - does not work

 

[joeu2004]

My locale is CZ, decimal separator is ","
I have a time in this format 2015-05-22 13:39:59.847
if I paste this value into excel, it reads the day correctly and display it as 22.5.2015 13:39:59 (decimal 42146,5694328704)
however, if miliseconds begins with 0, it fails to parse the date and just gets pasted as original text. Can't be formated with DATEVALUE function.
2015-05-22 13:39:59.047 - does not work
2015-05-22 13:39:59.000 - does not work

Works for me (US, English; Excel 2010), after I change the regional short-date form to yyyy-M-d. But....

If your decimal separator is "," (comma), why would you expect 59.847 (period) to work in the first place?

(I will try to experiment with my US-English version. But I'm not sure the results are comparable.)

Beware that Excel can be fickle when pasting from some sources, notably HTML. If 2015-05-22 13:39:59.047 is interpreted as text initially, try selecting the cell and pressing F2, Enter. I know: "why would that make a difference?". But it does sometimes. Klunk!

If F2,Enter does not repair it, try Text-To-Columns. It seems to be more tolerant of unexpected spaces sometimes. (Operative word is "sometimes".) And if that does not work, try the following in another cell:

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

The problems are: (a) some "spaces" might be nonbreaking spaces (HTML nbsp); and (b) sometimes Excel does not like leading, trailing and multiple spaces.

Finally, in case "pasted" means paste-special-value, be sure the "original text" and the format of the destination cell are not type text to begin with.

PS.... I forgot to mention.... Not sure what you mean by "can't be formatted by DATEVALUE". Did it return a #VALUE error? DATEVALUE would only interpret the date portion of the text, anyway. But you are correct insofar as the time portion must be in a valid form.