Re: [libreoffice-users] How to reverse a date format

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: users+unsubscribe@global.libreoffice.org
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