Convert wrongly inputted MDY General Format to DMY UK Date Format from one column to another

user_16162266 0
Viewed 1020

Our company's overseas 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?

enter image description here

excel
3 Answers

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 :

  1. 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 : Check this

enter image description here

See this.

user_172319960

You can also use Data | Text to columns like this:

enter image description here

Result:

enter image description here

I have put the results in a separate column for clarity, but if you accept the default it will convert the dates in-place.

user_38949170

I run across this when pasting data from files... So: I developed this - which copes with variable length day, month and year values (source MDYdate-as-text in table cell @dateField) (substitute the "/" if your date separator is not /):

=LET(srcDate,[@dateField],firstSep,FIND("/",srcDate),secondSep,FIND("/",srcDate,firstSep+1),
  DATE(
    RIGHT(srcDate,4),
    LEFT(srcDate,firstSep-1),
    MID(srcDate,firstSep+1,secondSep-firstSep-1)
  )
)

If the source date year is 2-digits, change the RIGHT(... to ,2).

However, because I have a replicated processing-table design that needs to cope with either of the source date-data formats, and sometimes variable length components with times, I extended the above to include a MDYDates switch reference to cope with either MDY or DMY [&time] on a per-table basis (each table's date-data is consistent from the source).

=LET(srcDate,[@dateField],
  firstSep,FIND("/",srcDate),secondSep,FIND("/",srcDate,firstSep+1),
  srcTime,IFERROR(FIND(" ",srcDate),LEN(srcDate)),
  lenYr,srcTime-1-secondSep,
  DATE(
    LEFT("20",4-lenYr)&MID(srcDate,secondSep+1,lenYr),
    IF(MDYDates,LEFT(srcDate,firstSep-1),MID(srcDate,firstSep+1,secondSep-firstSep-1)),
    IF(MDYDates,MID(srcDate,firstSep+1,secondSep-firstSep-1),LEFT(srcDate,firstSep-1))
  )
)

It is the Swiss army knife version of the formula, but it works on any MDY or DMY date text with or without time. It could be extended to cope with YMD...
Notes:

  • It presumes 21st century dates;
  • Edit the [@dateField] and you can paste it into Excel as is, or remove the line breaks etc.
  • The assigned variable names are long for this example. Once you have it working, edit them down (e.g. secondSep -> _s2) to save space and enhance recalc time.

Final thought: Excel tries to identify and convert dates by default. The above formulas will work consistently when the [@dateField] is explicitly formatted as Text, and then date-date is then pasted in as Values.

(Yes - this has been heavily edited from the original post yesterday.)

user_193971590
Related