full date string to date

classic Classic list List threaded Threaded
4 messages Options
bjlockie bjlockie
Reply | Threaded
Open this post in threaded view
|

full date string to date

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'


--
To unsubscribe e-mail to: [hidden email]
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
Alan Boba Alan Boba
Reply | Threaded
Open this post in threaded view
|

Re: full date string to date

James,

The only way I could get the text sample you provided to be accepted as a
date was to leave the day name off. Typing Wed, Jan 2, 2020 into a cell
resulted in a cell that contained text. Typing Jan 2, 2020 into a cell
resulted in a cell that contained a date in the default date format,
mm/dd/yy. That could then be formatted to NN, MMM D, YYYY and display as
Wed, Jan 2, 2020.

The DATEVALUE() function will convert the string Jan 2, 2020 to a date. It
returns Err:502 when pointed at the string Wed, Jan 2, 2020.

My suggestion would be to input the substring Jan 2, 2020 into the
DATEVALUE function then format as desired to get your dates back.

e.g. =DATEVALUE(MID(A1,6,20))

On Mon, Dec 30, 2019 at 2:55 PM James <[hidden email]> 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'
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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
>


--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011

--
To unsubscribe e-mail to: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: full date string to date

In reply to this post by bjlockie
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: [hidden email]
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

bjlockie bjlockie
Reply | Threaded
Open this post in threaded view
|

Re: full date string to date

In reply to this post by Alan Boba

On 2019-12-30 4:43 p.m., Alan B wrote:

> James,
>
> The only way I could get the text sample you provided to be accepted
> as a date was to leave the day name off. Typing Wed, Jan 2, 2020 into
> a cell resulted in a cell that contained text. Typing Jan 2, 2020 into
> a cell resulted in a cell that contained a date in the default date
> format, mm/dd/yy. That could then be formatted to NN, MMM D, YYYY and
> display as Wed, Jan 2, 2020.
>
> The DATEVALUE() function will convert the string Jan 2, 2020 to a
> date. It returns Err:502 when pointed at the string Wed, Jan 2, 2020.
>
> My suggestion would be to input the substring Jan 2, 2020 into the
> DATEVALUE function then format as desired to get your dates back.
>
> e.g. =DATEVALUE(MID(A1,6,20))

That works, thanks.

I have a spreadsheet with 2018 dates and I did a replace of '2018' for
'2019' to make a copy of the spreadsheet for 2019.
When I did the replace for 2020 dates it changed the cells to text.
I maybe had an option checked that I didn't before. :-(


>
> On Mon, Dec 30, 2019 at 2:55 PM James <[hidden email]
> <mailto:[hidden email]>> 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'
>
>
>     --
>     To unsubscribe e-mail to: [hidden email]
>     <mailto:users%[hidden email]>
>     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
>
>
>
> --
> Alan Boba
> CISSP, CCENT, ITIL v3 Foundations 2011
>

--
To unsubscribe e-mail to: [hidden email]
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