Our company's oversease partner sends us a long list of dates in MDY style. The problem is that these columns are formatted in Excel as General Format. For some reason, for example, Excel reads the manually inputted date of 8 November 2021 as 11 August 2021 because of this, i.e. the cell shows "11/08/2021". If they were formatted in US style Date Format, then the conversion would have been simple, but our overseas partner is an extremely large multinational corporation and they do not seem to bother fixing this issue.
I even tried using the =DATE(VALUE(RIGHT(XX,4)),VALUE(LEFT(XX,2)),VALUE(MID(XX,4,2))) and it returns "19/08/4422" for the aforementioned date.
We receive these documents almost every day and there are hundreds of dates each time. It is not efficient for us to manually use the Text-To-Columns option every day to fix these dates. Hence, I want to convert these date columns automatically to the proper date in another column. How should I go about this? Am I using the "=DATE" function wrongly?
CodePudding user response：
Change Excel Date Format from dd/mm/yyyy to mm/dd/yyyy To change the date display in Excel follow these steps:
Go to Format Cells > Custom Enter mm/dd/yyyy in the available space.
Or you can use :
- Select a blank cell next to your date, for instance. I1, and type this formula =TEXT(G1, "yyyy-mm-dd"), and press Enter key, then drag AutoFill handle over the cells needed this formula.
Sourse : 
See this.