Forum Discussion
can't read date format
I have a spreadsheet with imported data with date format like: 2023-11-9 12:16:38:609 I can change cell format to time but istext still says yes. timevalue and datevalue functions don't work. ...
SeanJohnsonv2 Try this:
=VALUE(SUBSTITUTE(ref,":",".",3))
Excel expects the third semi-colon to be a decimal point. "ref" is the cell where you have the text string.
This should transform the string into the number 45239.5115579745 which you can then format as a real date/time value.
- Thank you! That is certainly getting closer! My problem is needing to subtract two times. Using your suggestion I was able to see my two times as dates, but when I subtracted one from the other the answer was like 1.7e-6. Here's my values; what to do to get the proper answer of .147 s?
2023-11-9 12:16:38:609
2023-11-9 12:16:38:756Set the number format of the cell with the difference formula to hh:mm:ss.000 or to s.000 if you only want to see the seconds.
HansVogelaar That did it! Thanks for the learning!
