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 format is set up correctly, Excel seems to treat these cells as text.
In cell G6 I'm subtracting A5 from A6 and expect to get the answer 10.393 seconds (as shown in H6).
In cell G7 I'm subtracting A7 from A3 and expect to get the answer 126.168 seconds (as shown in H7).
Why isn't this working?
Here's what I'm trying to do...
I'm logging data points by taking pictures of the instrument screen. These photos have filenames including a date time stamp (like PXL_20230828_110444607.jpg). I type the data from the photos in columns B:E. In a command prompt I use DIR >datetime.csv. I then clean up the CSV file, open it with Excel, and copy - paste these 'values' into column A. Maybe there's an easier, or more appropriate, way to accomplish this task?
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.
21 Replies
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:
