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.
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...
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?
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?