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.
If you set the horizontal alignment of column A to General, you'll see that the values become left-aligned. This indicates that Excel sees them as text values instead of as time values.
Another telltale sign is that applying a different number format will have no effect on the way the values are displayed.
Solution:
- Select column A.
- On the Data tab of the ribbon, click Text to Columns.
- Without changing any settings, click Finish.
The values should now be right-aligned, and the subtraction formulas will work.
- Is it my machine?
I did what you said and all it did was delete the data in columns B:F... and no, the formulas still do not work.
Thank you for your help!This is what I got:
- 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!
