[Solved] Reformat dates (yymmdd without - or /)

[dbworrell]

I have a data file I was given with dates written in format yymmdd, but no - or / between. Is there a way to change this format to include the - or / quickly and easily?
I have over 2000 items and would like to avoid changing this data manually.

[FJCC]

You can do this with Find & Replace but try it on a copy of the file in case something goes wrong. Highlight the cells you want to affect. Select the menu Edit -> Find & Replace.
In the Search For box enter
(\d\d)(\d\d)(\d\d)
In the Replace With box enter
$1-$2-$3
Under More Options, select Current Selection Only and Regular Expressions. Click Replace All and you should be done. The cells will now contain text, not numeric dates. That may or may not fit your needs.
If you need numeric dates, a formula like this will convert what you have

Code: Select all

=DATE(VALUE("20" & LEFT(A1;2));VALUE(MID(A1;3;2));VALUE(RIGHT(A1;2)))

This also seems to work, though I am surprised it does, so I prefer the above.

Code: Select all

=DATE("20" & LEFT(A2;2);MID(A2;3;2);RIGHT(A2;2))

You can then format however you want with the menu Format -> Cells.

[Villeroy]

This also seems to work, though I am surprised it does, so I prefer the above.

This has been added to OOo 3.0 or something because so many Excel users try to calculate text.
The following works because the numeric text consist of digits only: ="002"+"3"
The DATE function in your formula expects 3 integers and accepts the textual integers.
The ugly concession to Excel works with pure digits and with ISO dates/times.
Excel may convert just anything numeric on the fly which leads to the effect that you get different calculation resutls depending on Excel's localization from the exact same spreadsheet with same values and same formulas.
Just like Excel, =SUM(A1:A99) ignores any text in A1:A99.
I think, it would be better if Calc would not try to measure up with Excel in this race of stupid anti-features.

[Lupp]

As with anything concerning textual "dates" given with 2-digit-year I would like to emphasize that no such dates actually exist. The notation can only describe a (huge) set of dates and needs thorough selection of the one to prefer.

Assuming you have a fix "century" coded by a text of two digits in B1, you get the completed date as a text in ISO 8601-extended format by

Code: Select all

=B1&MID(A3;1;2)&"-"& MID(A3;3;2) &"-" & MID(A3;5;2)

if A3 contains the mutilated date you got.

I would consider to leave it at that if no numerical calculations with these dates are intended. If needed you always can easily convert these dates to the numeric representation by DATEVALUE() (and by VALUE() as well).

[Villeroy]

Input: yymmdd

The following makes use of the global office setup for 2-digit dates:
US style date conversion: =VALUE(MID(A3;3;2) &"/"& MID(A3;5;2) &"/"& MID(A3;1;2))
Rest of the world date conversion: =VALUE( MID(A3;5;2) &"/" & MID(A3;3;2) &"/"& MID(A3;1;2))

See Tools>Options>OpenOffice>General: Two-digit years where you can define a threshold year for the 20th or 21st century (1930 by default).
And see Tools>Options>LanguageSettings>Languages: Locale (2nd option) which defines how your office suite converts text to numbers. English(USA) requires "mm/dd/yy".
Side note regarding LibreOffice Calc: The string pattern has to comply strictly with the additional "date acceptance pattern" in the language options (which is an anti-feature if you ask me).

Lupp's solution converts an ISO date yyyy-mm-dd which should work with all locale settings if the date is given as a 4-digit number.
The following variant switches between 20th and 21st century at the 2-digit year 30:

Code: Select all

=VALUE(IF(VALUE(MID(A3;1;2))>30 ; "19" ; "20") &MID(A3;1;2)&"-"& MID(A3;3;2) &"-" & MID(A3;5;2))

The ISO date with 4-digit year does even work with LibreOffice despite its user defined "date acceptance patterns". It is always a good idea to use ISO dates when dealing with date strings is inevitable.

Same trick with FJCC's appropach utillizing the DATE function:

Code: Select all

=DATE(IF(VALUE(MID(A3;1;2))>30 ; "19" ; "20") & MID(A3;1;2) ; MID(A3;3;2) ; MID(A3;5;2))

The following makes use of the two-digit-date setting in the options dialog:

Code: Select all

=DATE(MID(A3;1;2) ; MID(A3;3;2) ; MID(A3;5;2))

P.S. regarding the topic title "Reformat dates". This has nothing to do with formatting. We calculate from your 6-digit input another number. Dates in spreadsheets are day numbers. When you apply any of the date formats to a number, you get the exact same number displayed in a different fashion. 31 December 2014 is day number 42004. You can format 42004 any way you want, changing the display but without changing that value.
Your input can be converted into a localized string ("d/m/yyyy" or "m/d/yyyy" or "yyyy-m-d") and the VALUE function will convert that string into the right number. The ISO string with 4-digit year is unambiguous, the m/d/y and d/m/y variants are ambiguous. Their conversion depends on the appropriate locale setting.

Alternatively, you can convert the 6-digit string into 3 separate numbers for year, month and day which are handled by the DATE function. DATE converts a 2-digit year according to the corresponding global option. IMHO, the DATE function does the best job.

[MrProgrammer]

Hi, and welcome to the forum.

I have a data file I was given with dates written in format yymmdd, but no - or / between. Is there a way to change this format to include the - or / quickly and easily?

Yes! This is exactly what the Text to Columns feature does. See Q08/A08 in that link. The example has format YYYYMMDD instead of YYMMDD, but this detail won't matter. Using this feature, you won't need complicated formulas or confusing Find/Replace syntax to perform conversions to a date from a number (180521 looks like a number to Calc).

After the conversion, the dates will be displayed in the default date format for your locale, which might be M/D/YY. But Calc understands that they're dates so you can, say, sort them and 9/5/17 will come before 10/25/17. After the conversion you can set any desired date format using Format → Cells → Numbers → Category → Date → Format → {your choice}. Just select all the cells and set the display format.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Villeroy]

If you import the initial 6-digit dates from a text file or from clipboard, you can do the same as with the text-to-columns tool. Just mark the dates as YMD dates by right-clicking the column header in the preview.

Import text data with 6-digit dates