Calc: Another text to date question

I have a column of dates in the format “Tue Jun 1 09:15:00 AEST 2021”. I ultimately want to sort this column. Can I convert this text to date, or is there another way to sort it?

Was thinking firstly to remove the day name (Mon Tue etc) and also the timezone (AEST) might help but I’m new to Calc and don’t really know how to fiddle with data in cells…

Thanks for any help.

Thanks again for all the help, plenty of info to work with and all solutions so far seem to DTRT.

Totally unrelated… seems I have too much data for my poor laptop to handle and when I start manipulating the sheet Windows 10 comes to a screaming swap-induced halt (eventually recovers after an hour or so). 38698 rows, 3 columns.It’s only got 4GB RAM and Windows memory management seems lackluster.

ps. also apologies for forgetting to mention I wanted to sort the date column (and associated columns) in ascending order (earliest to latest) with the ultimate goal to make a graph of it. LibreOffice 7.1.3.2 x64. Locale: en-AU.

pps. how do I mark this as answered?

pps. how do I mark this as answered?

Click the check mark (:heavy_check_mark:) next to the correct answer (which fits best to your needs).

For the brave at heart ;p) assuming the example date string in A1:

=DATEVALUE(REGEX(A1;"(?i)^\w+ (\w+) (\d+) .+ (\d+)$";"$3-$1-$2"))+TIMEVALUE(REGEX(A1;".+(\d+:\d+:\d+).+";"$1"))

and pull the formula cell down. Then select result range and Copy & Paste Special Date+Time values with Formats only.

Note that for abbreviated month names like Jun to be recognized that has to run in an English locale.

The same is slightly shorter :slight_smile:

=–REGEX(A1,"(?i)^\w+ (\w+) (\d+) (\d+:\d+:\d+).+(\d+)$","$1 $2 $4 $3")

That may work or may not, it depends on the detailed calculation settings (Tools → Options → Calc → Formula) whether for conversion from text to number calculating with strings is allowed, and locale-dependent conversion must even be allowed as well, not only unambiguous conversion.

Thank you!

Fwiw, your

=REGEX(A1;"(?i)^\w+ (\w+) (\d+) (\d+:\d+:\d+).+(\d+)$";"$1 $2 $4 $3")

produces Jun 1 1 09:15:00 because the .+ before (\d+)$ is greedy.

At least please test your solutions. Also note we use the ; semicolon function parameter separator here because that is locale independent.

I’m sorry, I did not notice the difference between 2001 and 2021 when checking.

 =--REGEX(A1; "(?i)\w+ (\w+) (\d+) (\d+:\d+:\d+).+(\d{4})";"$1 $2 $4 $3")

Hope the following formula should work in any locale

=--REPLACE(REGEX(A1; "(?i)\w+ (\w+) (\d+) (\d+:\d+:\d+).+(\d{4})";"$4-$1-$2 $3");6;3;(SEARCH(MID(A1;6;2);"anebarprayunulugepctovec")+1)/2)

Only if detailed calculation settings conversion of text to numbers is not set to “Generate #VALUE! error” nor to “Treat as zero”.

Otherwise nice approach.

One needs to manually set the date+time format though, which is done automatically when using DATEVALUE()+TIMEVALUE().

Combining these two approaches is left as exercise to the reader :wink:

Thanks again for the detailed clarifications!

Hi
I think one method can help…

select cells > copy > click to other cell (just next column) > right click > Paste Special > Unformatted Text

Now, in Separator Options > Separated by > select (check box) Space > OK

Then you can merge important columns(3rd-2nd-last column with / as separator, &"/"&) to form a meaningful/system recognized form of date…

Now copy “time column” and “the above date - column” and Paste Special into new column as Unformatted Text, select hide columns other than time and date columns, select Column Type as Date for the date column…

Try sorting after selecting both columns…

Assuming Tue Jun 1 09:15:00 AEST 2021 is in cell A1 with others in the same column, you could select the column, and click Data > Text to columns. In the dialog that opens make sure Space is ticked under Separator options then click OK.

This will split each part into a single “word” so you could join the date parts together in cell G1 with the formula =VALUE(F3&"-"&B3&"-"&C3) which will return the date as a number.

Copy the formula down the column then copy column G and paste as text to make it independent from the originating cells. Then format the column in your preferred date style e.g. YYYY-MM-DD, or even MMM DD YYYY. You can delete the originating date cells

The time should be recognised as time and having AEST in a different column is best.