There is a variety of way to display a date in Excel. When working with dates, you could possibly get any format of the dates. If you get the date format written in "d.m.yyyy" format and you want to convert them into a different format such as "yyyy-m-d" or "yyyy-mm-dd", you will need to extract the year, the month and the day first, then combine them in a different order using the ampersand sign or the Concat Function.
1. From the format "d.m.yyyy" to "yyyy-m-d", please use the following formula and change A2 with the cell name in your file.
=RIGHT(A2,LEN(A2) – FIND(".",A2, FIND(".",A2)+1)) & "-" & SUBSTITUTE(MID(SUBSTITUTE("." & A2 & REPT(" ",10),".", REPT(".",300)), 2*300, 300),".","") & "-" & LEFT(A2, FIND(".",A2)-1)
The formula is long and here is the break down:
– RIGHT(A2,LEN(A2)-FIND(".",A2,FIND(".",A2)+1)): this is to extract the text after the second dot, which is the year in the text string;
– SUBSTITUTE(MID(SUBSTITUTE("."&A2&REPT(" ",10),".",REPT(".",300)),2*300,300),".",""): This is to find the text between two dots, please check "how to extract data between commas" for details, but to replace commas to dots.
– LEFT(A2,FIND(".",A2)-1): This is to find the text before the first dot, which is the day in the text string.
2. From the format "d.m.yyyy" to "yyyy-mm-dd", please use the following formula and change A2 with the cell name in your file.
=RIGHT(A2, LEN(A2) – FIND(".",A2, FIND(".",A2)+1)) & "-" & TEXT(SUBSTITUTE(MID(SUBSTITUTE("." & A2 & REPT(" ",10),".", REPT(".",300)), 2*300,300),".",""),"00") & "-" & TEXT(LEFT(A2, FIND(".", A2)-1),"00")
This is to format the month or day to a number with two digits when it comes with one digit. For example, January is 1 in number and will be formatted into 01.
