Converting from MM/DD/YYYY to DD/MM/YYYY (Native format)?

[CompSciSarah]

I’ve been trying to do this a few different ways, but I have a row that is pasted in the format of MM/DD/YYYY and I need it converted to DD/MM/YYYY (the locale of my system is set to UK which is what I need it as).

I’m not sure what to do, I have a function which manually transforms it using the MID function (but this breaks when the bottom is 12/1/1984 for some reason):
=DATEVALUE(MID(B142,4,2)&"/"&MID(B142,1,2)&"/"&MID(B142,7,2))

The second strategy I used is derived from searches online using “data → text to columns” which does nothing as far as I can tell despite the box being highlighted (highlight box → data → text to columns (with tab seperator) literally nothing happens.

How can I do this conversion?

[Villeroy]

Please upload the document. Remove any confidential information. Dates without context are not confidential.

[mikekaganski]

@CompSciSarah

pasted in the format of MM/DD/YYYY and I need it converted to DD/MM/YYYY

@CompSciSarah

this isn’t a static data set

The better (more proper) task is then not to convert the data, but to import it correctly.

When you paste the data, you might want to make sure to paste it as plain text.

Then (if it’s multi-line data) you will get a “Text Import” dialog, that allows you to define import settings, among them the date format of the pasted data. Say, you are pasting the next set:

01/01/2022
01/02/2022
...
01/15/2022
01/16/2022

You know that this data is in MDY format; but the program has no way of knowing that. Even analyzing the first 12 rows, it can’t decide. So it’s the task of user to inform the program about the date format - by right-clicking the respective column:

Another way of letting the program know what settings to use on import is setting the “Language” in the same dialog (the control name is wrong actually - it is locale, i.e. the set of data formatting rules to use when analyzing the data; tdf#138748). E.g., in your case, you know that you are importing the data that uses en-US conventions, so just select that “language”.

That way, the program knows how to interpret the imported data - note that this is not how you want it to display. Then it will recognize the dates, and you will get the proper display according to your preferences (and may further adjust the format using Format|Cells).

Alternatively, you may convert already imported data - Data|Text to Columns is for that. But here you need to understand this:

1. The initial import with wrong settings could already “recognize” some of the dates incorrectly - specifically those that were ambiguous, having day no greater than 12. Those will need special handling; it’s better to make sure you get proper results on paste, than trying to rectify the mess from incorrect import later.
2. (Maybe not related to your case, but worth noting) the Text to Columns function may distribute the data from the single column (it only operates on a single column!) to several columns (depends on your settings); and then you should remember that the data in the columns to the right may be re-written.

[Lupp]

@CompSciSarah

…but this breaks when the bottom is 12/1/1984 for some reason…

(“bottom” ?)
The “some reason” may be that your formula expects two digits in the DD position of the “US date”.
Using LibreOffice 6.2 or higher you might better use the REGEX() function in such a case.
disask72792datesAgain.ods (14.8 KB)

[Villeroy]

I assume that all your dates have been imported as text by mistake. You formula just concatenates another text. Dates in spreadsheets are numbers. Text dates sort alphabetically, can’t be used in calculations and most data analysis goes wrong with text dates. Your cell formatting shows second problem. By default any text is left aligned and numbers are aligned to the right cell border but you enforced all your data to be centered.

Call Tools>Options>Language Settings>Languages
Set the locale setting (2nd option) to English(USA). This is the locale of the text dates you have right now.
Now you can calculate the correct dates with =VALUE(A1). Do not bother about any formatting. It does not matter at all. All that counts is the correct number.
Convert the formula results to constant values with copy & paste-special numbers.
Switch back the locale to English(UK) or whatever you had before.
Apply any number format you prefer. If your global locale is still English(USA), you can override this locale in the number format dialog.

[CompSciSarah]

Oh, my bad, this isn’t a static data set.

I constantly have to convert from one to the other as I’m doing this as a part of my job. I use a fast convertsion sheet since the software doesn’t do it for me

So statically setting the locale won’t work for my uses. Although I now understand a lot better about the numbers thing as I have seen that before.

[Villeroy]

If your formula DATEDIFF(concatenation) converts the strings or not depends on the global locale setting in the options dialog.
@mikekaganski demonstrated how to import US English data with $ currencies and twisted dates correctly in order to avoid later conversion. Using the language option “English (USA)” you describe what you have, not what you want.
Another problem with badly imported US dates is that some date strings may be imported as numbers, however wrong numbers. For instance 01/02/2021 would be imported as 1st of February although the date is meant to be 2nd of January. Wrong dates are even more harmful than text dates.
This is why I wanted you to upload a copy of the concrete document rather than screenshots. Screenshots are pointless unless you have a display problem. A document tells everything about a document’s problem:
t72792.ods (28.3 KB)

[Zizi64]

It is better to use the international (ISO) standard Date format:
YYYY-MM-DD
That is recognizable at all of locale settings.

[erAck]

Maybe the conversion FAQ’s Find & Replace wrong date order helps.