Date: prev next · Thread: first prev next last
2017 Archives by date, by thread · List index


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

Context


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.