Thread: Sort datetime values

Hi,

Into which order are you wanting to sort it?

Rgds

Given AM/PM you will need to convert the strings to date time values in order to sort.

If we assume your default date format is dd mm rather than mm dd then perhaps you could use:

Code:

B1: =--(MID(A1,4,2)&"-"&REPLACE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,26),".","-"),":",".",3),4,3,""))
copied down

to generate the time based values -- against which you can apply the sort.

I have a CSV file that contains data in JAVA SimpleDateFormat as listed above, This is one of the column in the file, and I need to use this date field for generating some dynamic charts (excel OFFSET)

I require my column to be sorted in ascending order.

I tried formatting the column to date/Time/Custom types.. but it didnt help

Maybe =LEFT(SUBSTITUTE(A1, ".", "-"), 19) + MID(A1, 21, 3)/86400000 + ISNUMBER(FIND(" PM ", A1))/2

Ignores the GMT offset.

The formula provided previously converts the Time correctly (if your standard date format is day month rather than month day)
EDIT: shg's will work only if the opposite is true (ie month day standard format)

The AM/PM in your resulting values is down to the format you have applied to the data.

If I sort the values you list in your prior post (ie formulae results) they are listed in order as expected - ie for me they are numerics - we're assuming the same is true for you (ie no #VALUE! output)

Like shg's formula the earlier formula assumes the GMT offset is consistent, ie is ignored.

If you need further assistance post a file.

I would suggest perhaps trying the actual formula provided earlier...

Note specifically those characters that appear post equals sign in my formula but that are missing in yours.

Really appreciate your quick response, time and effort in helping me on the issue , actually I was getting an error with the two sign's, Can you elaborate on the formula or point me to some link, which I can refer to understand the process of converting string to excel date format

If you dont mind can you share your worksheet

If you get a #VALUE! error then the implication is that your default date format is in fact month day year rather than day month year, in which case try either shg's version or:

Code:

B2: =--SUBSTITUTE(SUBSTITUTE(LEFT(A2,26),".","-"),":",".",3)

If the above also returns #VALUE! then try:

Code:

B2: =--SUBSTITUTE(LEFT(A2,26),":",".",3)

If neither of the above work you will need to detail your default date format.
ie - if you enter a datetime value into a cell in what format are you entering it ?