Apple Numbers: Convert Date format M/D/YY or D/M/YY to YYYY-MM-DD

Question

Note: I had this question, couldn't find an answer and can provide the answer myself. I hope this can be used by others too.

Using Apple Numbers, how can I convert the date format of M/D/YY to YYYY-MM-DD by using a formula?

M/D/YY Examples:

• 12/31/17 » 2017-12-31
• 1/1/17 » 2017-01-01

D/M/YY Examples:

• 31/12/17 » 2017-12-31
• 1/1/17 » 2017-01-01

Answer 1

Entire formula (including year, month, day) while referencing table cell A1 (in this example) having the date in either M/D/YY or D/M/YY:

M/D/YY to YYYY-MM-DD

"20"&RIGHT(A1;2)&"-"&IF(LEN(MID(A1;1;FIND("/";A1;1)−1))=1;"0"&MID(A1;1;FIND("/";A1;1)−1);MID(A1;1;FIND("/";A1;1)−1))&"-"&IF(LEN(MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1))=1;"0"&MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1);MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1))

D/M/YY to YYYY-MM-DD

"20"&RIGHT(A1;2)&"-"&IF(LEN(MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1))=1;"0"&MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1);MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1))&"-"&IF(LEN(MID(A1;1;FIND("/";A1;1)−1))=1;"0"&MID(A1;1;FIND("/";A1;1)−1);MID(A1;1;FIND("/";A1;1)−1))

---

Single fields

For some background and to extract the individual fields:

• Year: "20"&RIGHT(A1;2)
• First Number (Month or Day): IF(LEN(MID(A1;1;FIND("/";A1;1)−1))=1;"0"&MID(A1;1;FIND("/";A1;1)−1);MID(A1;1;FIND("/";A1;1)−1))
• Second Number (Month or Day): IF(LEN(MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1))=1;"0"&MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1);MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)−FIND("/";A1)−1))