CODEWITHSUNDEEP
datedatetimegoogle-sheets
SL8t7
SL8t7

Reputation: 647

Convert Timestamp string (dd MMM yyyy HH:mm:ss) to Date (DD/MM/YY) in Google Sheets

I have a Google sheet with a column populated with a DateTime (unfortunately missing the leading 0 in the case of single-digit dates).

For example:

I want to convert these to the following format:

I have tried:

=DATE(MID(C2,7,4), MONTH(MID(C2, 3, 3)&1), REGEXEXTRACT(C2, "\d+"))

And while that works fine for the single digits, it's causing an error when double-digit days come up (should have said, I want this to work in an arrayformula really, or at least have one formula I can copy down the 21k rows)

Anyone have any ideas?

Upvotes: 0

Views: 2094

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(TEXT(INDEX(SPLIT(C2:C, ","),,1), "dd/mm/yyyy"))

enter image description here

Upvotes: 1

Related Questions