CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Tmel
Tmel

Reputation: 1

How to convert a text string in Google Sheets to date format

I have a column with dates and time formatted like this in each cell:

Thursday, Jan 21, 2021 4:30 PM-5:00 PM

I want to split this across two columns so that the first column has "DD/MM/YY" and the second has the timeslot.

So it would go from being a cell with:

Thursday, Jan 21, 2021 4:30 PM-5:00 PM

to two cells:

21/01/21 4:30 PM-5:00 PM

What formula can I use in Google Sheets to achieve this?

Upvotes: 0

Views: 1410

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

Another suggestion (which assumes here that your raw data runs A2:A):

=ArrayFormula(IF(A2:A="",,SPLIT(REGEXREPLACE(A2:A,"^\w+, (.+\d) (\d.+$)","$1~$2"),"~")))

This will leave your dates in the first column as numeric raw dates rather than as text, so you'd be able to use them in calculations and comparisons later. Just select the first column of the results (i.e., those raw dates, showing as numbers in th 40000 range) and format the entire column (Format > Number) in the date format you prefer.

Upvotes: 3

player0
player0

Reputation: 1

use:

=INDEX(IFNA(TEXT({REGEXEXTRACT(A1:A, ", (.+\d{4})")*1, 
 REGEXEXTRACT(A1:A, "\d{4} (.+)")}, {"dd/mm/yyyy", "@"})))

enter image description here

Upvotes: 1

Related Questions