Site icon ExcelNotes

How to Change Date Format dd.mm.yyyy to yyyy-mm-dd

There are variety of way to display a date in Excel. If you get the date format written in "dd.mm.yyyy" format and you want to convert them into a different format such as "yyyy-mm-dd", you will need to extract the year, the month and the day first, then sort them to "yyyy-mm-dd" format with the ampersand sign or the Concat Function.

From the format "dd.mm.yyyy" to "yyyy-mm-dd", please use the following formula and change A2 with the cell name in your file.

=RIGHT(A2,4) & "-" & MID(A2,4,2) & "-" & LEFT(A2,2)

where RIGHT(A2,4) is to get the year, MID(A2,4,2) is to get the month, and LEFT(A2,2) is to get the day.

If you want to convert the format "dd.mm.yyyy" to "mm/dd/yyyy", please use the following formula and change A2 with the cell name in your file.

=MID(A2,4,2) & "/" & LEFT(A2,2) & "/" & RIGHT(A2,4)

Exit mobile version