Convert Text to Date

[aronlibre]

Hi, I am attempting to convert Text columns to Date.

My Libre Office Spreadsheet has data like
20100520
20100606

I have tried two things based on things I Googled.

1. Format → Cells → Date → Format Code YYYYMMDD generates error “#FMT”
2. Tools → LibreOffice Calc → Calculate does not seem to do anything

If this matters, I am trying to subtract, so I want to see 20100606 - 20100520 equal 17 (days) vs 86. I am not expert so feel free to dumb answers down if needed!

Appreciate any help.

Aron

[erAck]

If the YYYYMMDD format code displays #FMT then 20100520 is not of type Text but Numeric, just a number. It would be 20100520 days after the null-date 1899-12-30 …

Anyhow, to convert to date, select the data range, press Ctrl+H to invoke the Find&Replace dialog:

• Find: (\d{4})(\d{2})(\d{2})
• Replace: $1-$2-$3
• Other options:
  • Current selection only
  • Regular expressions

Hit ReplaceAll.

See also this FAQ.

[dscheikey]

Du you whant tu use this formula in a helpercolumn?

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Cell A1 = 20100520
Date = 20 May 2010

[LeroyG]

1. Select the dates range (one range at a time)
2. Choose menu Data - Text to Columns
   2 1/2: “click on the required column in the preview area” [thanks to @mikekaganski comment]
3. Change the Column type to Date (YMD)
4. Format your data as Date (menu Format - Cells - Numbers tab)

No matter if your original data is formatted as text or number.

Tested with:
Version: 7.3.6.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 1; OS: Linux 5.14; UI render: default; VCL: gtk3
Locale: es-MX (en_US.UTF-8); UI: en-US
Calc: threaded

[aronlibre]

Thanks @dscheikey and @erAck these both worked for me!

[aronlibre]

@LeroyG the Column Type was grayed out so I could not use but appreciate the reply. Aron

[mikekaganski]

@aronlibre

the Column Type was grayed out

… because steps in @LeroyG’s answer missed step 2 1/2: “click on the required column in the preview area”

tdf#150652