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+unsubscr...@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
