CODEWITHSUNDEEP
google-sheets
Edmund Ong
Edmund Ong

Reputation: 61

Switch date and month google sheet

I have a data column which date in this format 05/12/2021 = dd/mm/yyyy but google sheet see it as 05/12/2021 = mm/dd/yyyy, how can I use a formula to switch the day and month around and display as 5/12/2021 = d/mm/yyyy. It has to be done on a formula level as there are many other dates column that has the format and information right.

Formula tried

=TEXT(A3:A,"d/mm/yyyy")

Upvotes: 1

Views: 3414

Answers (1)

Aresvik
Aresvik

Reputation: 4620

If the date is entered correctly, you should be able to format it by going to the 'Format' menu.

However, if you need to transpose the formatted day and month using formula, then try this in row 3 (any column but A):

=arrayformula(datevalue(regexreplace(to_text(A3:A),"(.|..)[\/\-\.](.|..)[\/\-\.](.*)","$2\/$1\/$3")))

Upvotes: 3

Related Questions