On Mon, Jul 17, 2017 at 7:38 AM, Brian Barker <[email protected]> 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: [email protected] 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
