[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

References:
[libreoffice-users] full date string to dateJames <bjlockie@lockie.ca>
Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.