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.
I see, thank you. However, even if I have text file like
an copy/paste data in Excel, it correctly recognises it as datetime
Perhaps you may share your text file (just few records) to play with it. Workaround exists, but it's not clear why your Excel doesn't convert values correctly.
I've attached the text file... sorry, but I had to ZIP the file to upload...
You say a "workaround exists", but not for me!
- Wild? Yes! Correct? Yes!
Excel was apparently interpreting the input as a date, even though I custom formattted it as a time. Oh, yes. I set my date format to
and have texts as in your case
Returning back to
works as expected
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.
You guys have been a great help, but alas...
I get the same behavior with "excel /safe" (but I learned something new - didn't know about this).
I may have an unusual system date format for the USA, but it's certainly not unreasonable. Here's everything:Most probably that's related to regional settings, but what you share is the same as on my or HansVogelaar computer, and we have correct transforming into the datetime.
Minor chance it's something outside, you may try to repeat the same in Excel safe mode, i.e. Win+R, excel /safe and here to repeat copy/paste into the empty sheet.
What are the settings in the Currency and Time tabs of the Customize Settings dialog in Windows? (You showed the Number tab higher up in this discussion).
- Yes! I'm convinced it is a problem with my setup, not Excel.
Does anyone know what settings, in Excel or the registry, might cause the behavior I'm experiencing?