How to reverse a date format

classic Classic list List threaded Threaded
5 messages Options
Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to reverse a date format

This spread sheet has several columns of dates
written MM/DD/YYYY. I need to reverse it to
YYYY/MM/DD. This is what I came up with:

=MID(A17,FIND("/",A17)+4,1024)&"/"&IF(LEN(LEFT(A14,FIND("/",A14)-1))=1,LEFT(A14,FIND("/",A14)-2)&"0"&LEFT(A14,FIND("/",A14)-1))&"/"&MID(A17,FIND("/",A17)+1,2)

I created this using one column as my example
not realizing the other columns dates were
created at a later date and formatted differently.

The column I used the format was Number general.
Another column is Number -//1234 with a format
code of #"/"##"/"####  and another of Date
12/31/1999 format.

This formula will not work on the second and third
columns. Any suggestions how to adapt this or
other ideas?


--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
V Stuart Foote V Stuart Foote
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to reverse a date format

Wade Smart wrote
This spread sheet has several columns of dates
written MM/DD/YYYY. I need to reverse it to
YYYY/MM/DD...
You can not change the internal format of the dates.

If they are columns of Dates in an ODF spreadsheet they do not need to be changed--actually can not be changed--rather you just need to change their display format. Select the column, the Format -> Cells  and in the dialog select Date and a format you need. The YYYY-MM-DD is predefined. Or you can add a format code to use an alternate separator.

If the file is CSV, set it as date during import and set the display format needed.
Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to reverse a date format

On Mon, Jul 17, 2017 at 7:26 AM, V Stuart Foote <[hidden email]> wrote:

> Wade Smart wrote
>> This spread sheet has several columns of dates
>> written MM/DD/YYYY. I need to reverse it to
>> YYYY/MM/DD...
>
> You can not change the internal format of the dates.
>
> If they are columns of Dates in an ODF spreadsheet they do not need to be
> changed--actually can not be changed--rather you just need to change their
> display format. Select the column, the Format -> Cells  and in the dialog
> select Date and a format you need. The YYYY-MM-DD is predefined. Or you can
> add a format code to use an alternate separator.
>
> If the file is CSV, set it as date during import and set the display format
> needed.

Well, that fixes one column.
Ill have to figure out the rest then.

Thanks for the information.

--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to reverse a date format

In reply to this post by Wade Smart
At 06:26 17/07/2017 -0500, Wade Smart wrote:
>This spread sheet has several columns of dates written MM/DD/YYYY. I
>need to reverse it to YYYY/MM/DD. This is what I came up with:
>=MID(A17,FIND("/",A17)+4,1024)&"/"&IF(LEN(LEFT(A14,FIND("/",A14)-1))=1,LEFT(A14,FIND("/",A14)-2)&"0"&LEFT(A14,FIND("/",A14)-1))&"/"&MID(A17,FIND("/",A17)+1,2)

I'm guessing that you mean that this is for dates written as text,
not proper spreadsheet dates as internal numbers formatted to show as
you describe. I'd consider doing this a different way.
o Select the relevant column.
o Go to Data | Text to Columns... .
o Under Fields, click the column header.
o For Column type, select "Date (MDY)" (yes: really!).
o OK.
Your dates are now in internal numeric format and can be formatted as
YYYY/MM/DD if you wish.

>I created this using one column as my example not realizing the
>other columns dates were created at a later date and formatted
>differently. The column I used the format was Number general.
>Another column is Number -//1234 with a format code of #"/"##"/"#### ...

The above technique should work with these values, too.

>... and another of Date 12/31/1999 format.

Leave those as they are.

You now have two obvious choices: either
o Leave your values as normal spreadsheet dates and format the
relevant cells as YYYY/MM/DD to show as you wish, or
o Use a formula such as =TEXT(Xn;"YYYY/MM/DD") to derive an explicit
text version of your values.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to reverse a date format

In reply to this post by Wade Smart
On Mon, Jul 17, 2017 at 7:38 AM, Brian Barker <[hidden email]> wrote:

> At 06:26 17/07/2017 -0500, Wade Smart wrote:
>>
>> This spread sheet has several columns of dates written MM/DD/YYYY. I need
>> to reverse it to YYYY/MM/DD. This is what I came up with:
>>
>> =MID(A17,FIND("/",A17)+4,1024)&"/"&IF(LEN(LEFT(A14,FIND("/",A14)-1))=1,LEFT(A14,FIND("/",A14)-2)&"0"&LEFT(A14,FIND("/",A14)-1))&"/"&MID(A17,FIND("/",A17)+1,2)
>
>
> I'm guessing that you mean that this is for dates written as text, not
> proper spreadsheet dates as internal numbers formatted to show as you
> describe. I'd consider doing this a different way.
> o Select the relevant column.
> o Go to Data | Text to Columns... .
> o Under Fields, click the column header.
> o For Column type, select "Date (MDY)" (yes: really!).
> o OK.
> Your dates are now in internal numeric format and can be formatted as
> YYYY/MM/DD if you wish.
>
>> I created this using one column as my example not realizing the other
>> columns dates were created at a later date and formatted differently. The
>> column I used the format was Number general. Another column is Number
>> -//1234 with a format code of #"/"##"/"#### ...
>
>
> The above technique should work with these values, too.
>
>> ... and another of Date 12/31/1999 format.
>
>
> Leave those as they are.
>
> You now have two obvious choices: either
> o Leave your values as normal spreadsheet dates and format the relevant
> cells as YYYY/MM/DD to show as you wish, or
> o Use a formula such as =TEXT(Xn;"YYYY/MM/DD") to derive an explicit text
> version of your values.
>
> I trust this helps.
>
> Brian Barkero this list will be publicly archived and cannot be
> deleted
>

HA! That worked!
You way was much easier than that long formula I did :D
Thanks for that. I very much appreciate it.

Wade
--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Loading...