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.
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.
- 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? 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!
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.
- Just to answer your question...
The temp, power, amps, volts were entered in Excel. The R is calculated. The times came from a CR-LF separated text file. I copied the text file and pasted it into Excel (although importing a CSV did the same thing for me).
Just to match you, I tried the Time mm:ss.0 format and it still doesn't work. Remove the decimal value and the cell becomes a value; add the decimal value and it turns back into text. Sorry, they are the same as system ones, thus doesn't matter. I tried to reproduce your case and was able to receive something similar if only to play with separators. However, is they are as you noticed, csv file like
if opened in Excel correctly shows datetime (as a number, not text)
the only is to apply proper format. Have no other ideas why it is picked-up as text in your case.