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


On Mon, Jul 17, 2017 at 7:38 AM, Brian Barker <b.m.barker@btinternet.com> 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: 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.