[libreoffice-users] How to reverse a date format

[Wade Smart]

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

[V Stuart Foote]

Post by Wade Smart
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.




--
View this message in context: http://nabble.documentfoundation.org/How-to-reverse-a-date-format-tp4218314p4218318.html
Sent from the Users mailing list archive at Nabble.com.

[Wade Smart]

Post by V Stuart Foote

Post by Wade Smart
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

[Brian Barker]

This spread sheet has several columns of dates written MM/DD/YYYY. I
=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

[Wade Smart]

This spread sheet has several columns of dates written MM/DD/YYYY. I need
=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