Nov 17 2023 06:53 AM
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?
Nov 17 2023 07:21 AM - edited Nov 17 2023 07:21 AM
@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.
Nov 17 2023 07:35 AM
Nov 18 2023 02:23 AM
Set 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.
Nov 20 2023 04:29 AM
@HansVogelaar That did it! Thanks for the learning!
Nov 22 2023 06:38 AM