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
1 Answers
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")
)
Related