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. splitting the columns into date and text (no option for time), and timevalue still doesn't work.
What am I doing wrong? What is wrong with that format that it cannot be converted?
- One important note: After I applied the s:000 formatting, the cells looked like they were in seconds. However, when I was doing some time delta/acceleration calculations, the answers looked very small, on the order of e-6. I realized that although the format was s:000, Excel still saw that as a day, and there are 86,400 seconds in a day. So a little math straightened that issue.
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.
