Forum Discussion
Solved
Excel will not subtract times with decimal seconds.
The attached XLSX file has times with decimal seconds in column A. The format for these is hh:mm:ss.000 (to attempt to show "eleven oh four and 44.607 seconds" in cell A3). While I believe the time...
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.
The problem is apparently with my machine... or should I say registry?
This solution apparently works on every other machine tested. I was advised to uninstall Office 365 and then reinstall. This did not correct the problem for me, leading me to think there is some setting 'stuck' in the registry.
Anyone who might know how to correct the issue on my machine are invited to comment!
This solution apparently works on every other machine tested. I was advised to uninstall Office 365 and then reinstall. This did not correct the problem for me, leading me to think there is some setting 'stuck' in the registry.
Anyone who might know how to correct the issue on my machine are invited to comment!
To be sure, what are you list, thousand and decimal separators on this machine?
- File > Options > Advanced has a period "." for a decimal and a comma "," for thousands. The Windows 10 OS is the same. I'll try swapping this to European style on a working machine and see if it still works...
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.
- 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?