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)
Hi, I have tried to use this formula, but I keep getting an error message:
"There's a problem with this formula."
I have typed is as follows: =RIGHT(A2,4) &"-"& MID(A2,4,2)&"-"& LEFT(A2,2)
I have included spaces as above, and also removed spaces, put my data in the same cells as your example – but still no joy. What have I not included? Any assistance would be hugely appreciated.
I tried again and did not see the problems. One possible reason I can think is that your data is in the DATE FORMAT, not in the text format. If that is the case, please convert the date into TEXT format first with the formula =Text(A2,"YYYYMMDD"), where "YYYYMMDD" can change to "DD.MM.YYYY", or check https://excelnotes.com/convert-date-into-yyyymmdd-text-string/, or directly use custom format if they are already in the date format:)
thank you!