Forum Discussion
Excel will not subtract times with decimal seconds.
It's a wild guess, but try setting the Short Date format to yyyy-mm-dd or yyyy/mm/dd.
If it doesn't help, simply set it to yyyy.mm.dd again.
To be sure, what are you list, thousand and decimal separators on this machine?
That works if "Use system separator" is unchecked
I'd check Windows regional settings as well, especially List separator
and are they the same as for Excel for web.
SergeiBaklan I'm sorry... are you saying it WILL work with the setting unchecked?
I ask because I do have this unchecked:
And the separators above match those of the OS:
I'm not sure what you're stating...
Just to confirm, I checked "Use system separators" and this did not work either.
Sorry, they are the same as system ones, thus doesn't matter. I tried to reproduce your case and was able to receive something similar if only to play with separators. However, is they are as you noticed, csv file like
if opened in Excel correctly shows datetime (as a number, not text)
the only is to apply proper format. Have no other ideas why it is picked-up as text in your case.
- OK, so if I put the file on OneDrive and open it from my web browser I can run the < Data > Text to Columns > command and it works.
Going back to the desktop Excel I see something I didn't share before... running the < Data > Text to Columns > DOES convert the text to a number, but it has rounded all of the times to the nearest second, populated the decimal portion with .000, and added AM at the end (even though I have the custom format with hh: for 24 hour time). Adding the decimal portion back, rather than the .000, makes the "number" go back to "text".
Any clues what is happening here?