Re: [libreoffice-users] full date string to date

At 14:39 30/12/2019 -0500, James Lockie wrote:
> I had a spreadsheet with dates that I think somehow got converted to 
> text. I tried pasting it as plain text with detecting special 
> numbers and I tried setting the column to a date format but it still 
> seems to come out as text. 'Wed, Jan 2, 2020'

Dates are sensitive to language and locale, so no guarantees, but 
...; you should be able to convert your data using the spreadsheet 
program's own facilities. Try this:

o Suppose your date data is in column A. Select the range (or column) 
and go to Data | Text to Columns... .  Under Separator options, tick 
Comma, Space, and Merge delimiters. OK. You now have the four parts 
of your dates separately in columns A, B, C, and D.

o In the first row of your data in a new column, enter (for, say, row 1)
=DATEVALUE(C1&B1&D1)
- and fill down the column. Note the jumbled order of the parameters, 
so what is offered to the DATEVALUE() function is three parts of your 
date concatenated as "2Jan2020".

o Format the values in the new column as desired, perhaps as
NN, MMM D, YYYY

You can copy the resulting values back over the originals if you wish 
- or elsewhere, of course - using Paste Special with Formulae unticked.

Oh, and by the way, unless something very strange is about to happen, 
I'm expecting 2 January 2020 to be a Thursday, not a Wednesday!

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy