Convert trading datas, 'date and time'

Here is another approach using the same basic idea (the same text functions). Thankfully the time of day can be coaxed out in one step by multiplying that part of the string by 1.

=DATE(TEXTBETWEEN(A2,"/"," ",2),TEXTBETWEEN(A2,"/","/"),TEXTBEFORE(A2,"/"))+TEXTAFTER(A2," ")×1

Day is the text before the first "/"

Month is the text between the two "/"

Year is the text after the second "/" and before the " "

Time of day is the text after the " ", multiplied by 1.

If you want to sort by the time only, you need to create a column that is just the time. Unless you absolutely need the date and time stuck together, you can make one column for the date and the next column for the time. To sort by date and time you can use the Organize sidebar to make a sort that does just that (i.e., sorts by multiple columns) or I think you can sort by the time column then sort by the date column to accomplish the same thing, but don't hold me to that.

I'll go out on a limb here and say that it does look like a bug - any date input with milliseconds is converted to text and/or automatic. While there are custom cell formatting options to display milliseconds, I couldn't find a reliable way of making it work, which matches your finding.

I was able to construct a table that took the date field as you provided, used a number of text parsing functions (mainly TEXTBEFORE(), TEXTBETWEEN() and TEXTAFTER() ) to break out the components, then used a combination of DATE() and DURATION() to create a date field that could be used in other functions.

This table shows how I broke the components down:

You can copy the individual components' formulas as-is, or incorporate them into one single function that looks like:

=DATE(TEXTAFTER(TEXTBEFORE(A2," ",1),"/",2),TEXTBETWEEN(TEXTBEFORE(A2," ",1),"/","/",1,1),TEXTBEFORE(TEXTBEFORE(A2," ",1),"/",1))+DURATION(0,0,TEXTBEFORE(TEXTAFTER(A2," ",1),":",1),TEXTBETWEEN(TEXTAFTER(A2," ",1),":",":",1,1),TEXTBETWEEN(TEXTAFTER(A2," ",1),":",".",2,1),TEXTAFTER(TEXTAFTER(A2," ",1),".",1))

Note that any text parsing is dependent on the input format. Since this is automated, there shouldn't be too much problem, but any changes in the input format may affect the results.

Create a column with this formula in row 2 (assuming your date&time strings are in column A)

=TEXTAFTER(A2," ")×1

fill down

Sort or filter on this column. The values are durations, not time of day, so the filter will be based on duration, not time of day.

> Not sure how to filter the ‘time’ only

You need to have a specific columns with just the time data.

You'll need to use some formula based on my earlier to table to extract the time component and construct a new TIME() field, then you can sort on this.

The TIME() function takes an hour, minute and second parameter, and I gave an earlier example of how to extract these components.

> Now, how can I sort based on time only?

In order to sort the table, you need to have a specific column to sort on.

If you want to sort by time, you'll need to add a column that extracts the time component from the imported data (you can use my earlier examples to see how to do this).

Once you have a column with times, you can sort the table by that column.

I figured it was worth a try. It looked exactly like a duration but was text. Multiplying by 1 converts text "numbers" so why not durations too, right?

If you saw my earlier post there is a complete formula for the date & time. Same basic approach as Camelot provided but all in one formula.