CODEWITHSUNDEEP
excel-formula
Laura Pilkington
Laura Pilkington

Reputation: 41

How to convert date to dd/mm/yy in Excel

I am working with Amazon vendor central and downloading order data in csv format.

The date formats are as follows;

Jan 3, 2022 8:00:00 PM GMT

Please can anyone help me with a formula to convert to dd/mm/yy format?

Thanks Laura

Upvotes: 2

Views: 10770

Answers (2)

Frank Ball
Frank Ball

Reputation: 1126

Let's try parsing the string, rearranging it and then putting back together as a date value:

=DATEVALUE(TEXTJOIN(" ",TRUE,CHOOSECOLS(TEXTSPLIT(SUBSTITUTE(H5,",","")," "),2,1,3)))

SUBSTITUTE removes the "," (it's not needed and creates problems later on). TEXTSPLIT splits the text using a " " as a delimiter. CHOOSECOLS selects the specific fields in the order you want them. TEXTJOIN assembles the selected fields into a " " separated string. DATEVALUE converts the string to a date value. At this point, you can apply whatever date format you want.

Upvotes: 0

Tragamor
Tragamor

Reputation: 3634

You can use MID() and FIND() to pull out the data to enter into a DATE(year, month, day) or DATEVALUE() formula then use TEXT(date, "DD/MM/YY") to format it.

The DATEVALUE Function can parse text as a date; but it seems it needs to be formatted "3 Jan 2022" as opposed to "Jan 3, 2022"

I have used an array rather than MID() to pull out the details and the formula for that is:

=LET(date, E3,
values, FILTERXML("<t><s>"&SUBSTITUTE(date, " ","</s><s>")&"</s></t>","//s"),
TEXT(DATEVALUE(SUBSTITUTE(INDEX(values, 2), ",", " ") & INDEX(values, 1) & " " & INDEX(values, 3)), "DD/MM/YY")
)

Upvotes: 0

Related Questions