Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
Hello, Here are my thoughts written after the > sign in response to Brian Barker's ideas: Spreadsheets generally used for calculation, so preventing it would usually be considered a drawback, not an advantage. You could put text values into word processor tables instead. But chacun à son goût. > I forgot to mention: My colleagues use the computer like a typewriter. Hold on: "BC 1000.01.01. to BC .12.31." makes no sense, as your start date is after your end date! Do you mean BC .12.31. to BC 1000.01.01.? But that starts at the end if the first year and finishes at the beginning of the end year, losing all but one day each of those two years. So perhaps you mean BC .01.01. to BC 1000.12.31.? That's better, but it still leaves you with what would be a roughly twenty-thousand year range - but with a strange central gap of 1998 years, from 999 BC (BCE) to AD 999 (CE) inclusive. I can't imagine you mean that. > My suggestion was wrong. The date range should be:> BC .01.01. to AD .12.31.> The missing range should be in this form:> e.g. BC 0012.01.01. > 1st January 12 > e.g. AD 0341.01.01. > 1st January 341> e.g. AD 0016.01.01. > 1st January 16 > The leading zeros should help in sorting the dates. Spreadsheets have always been able to handle text. I suspect most spreadsheet users would not see selecting text as a data type to be a workaround, beautiful or otherwise. > My colleagues use the computer like a typewriter. That is why they want to input the date format as it looks.> Another problem is that there are different computer systems, e.g. Windows XP, Windows 7, Office 2003, Office 2007, Office 2010. Unfortunately, Microsoft products produce inconsistent results as usual in my environment. LibreOffice is changing and improving. > I consider recommending LibreOffice to my boss after making sure that it works consistently. People make mistakes. One obvious limitation is that non-existent dates can be entered as easily as real ones. Entering "2015.02.29." as text creates something looking as much like a date as does "2015.02.28.", whereas entering these (supposed) dates normally shows one as a right-aligned date and the other as left-aligned text. Only Erich Kästner and perhaps the Tiananmen Square protestors are allowed the 35th of May. > Absolutely. Exactly a typist and typewriter can produce typos.> A typist needs to accept no automatic check for non-existent date, e.g. 44 January 2015.> My boss appears to require the staff's human check instead of automatic check. But I say again: chacun à son goût. > I consider convincing my boss to use the automatic format. Custom format code: "."MM"."DD"." which I know will produce 2015.01.01. without problems in LibreOffice.> It is very hard for me to convice my boss.> I don't know if there is any tutorial teaching me how to convince a typist to use the automatic tool. The typist is afraid of changing formats when the automatic tool is enabled.> Computer software changes all the time. I myself cannot guarantee that the format will stay the same. Can the LibreOffice community "guarantee" the static format of date? Alternatively, can the community make a design standard to let the users know that the future versions of LibreOffice will handle the dates in the same way? How about quality assurance? Can the quality assurance people help to check this date handling consistency before releasing another improved version of LibreOffice.> I am sure that with the "guarantee" or "best practice of design", my colleagues (the typists) and I will be a bit comfortable in using LibreOffice for internal computing at least.> Thanks for ideas. Best wishes,C. H. D. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
At 03:59 01/01/2015 +, Conly Honly Donly wrote: At last, I knew how my colleagues strongly believed and what they wanted to achieve. They wanted a date format which works across all known free, paid, old and new versions of spreadsheet program, e.g. LibreOffice Calc. They decided to use .MM.DD. and MM.DD., e.g. 2015.01.01. ---> three dots. 1st January, 2015. and 01.01. ---> two dots. 1st January. This format is standardized internally for communication. The good things are: (1) The date is stored as "text" or "string" on the computer. It is not a "number" to the computer any more. Adding or subtracting dates is disabled. It is good for just displaying the dates, which my colleagues wanted. Spreadsheets generally used for calculation, so preventing it would usually be considered a drawback, not an advantage. You could put text values into word processor tables instead. But chacun à son goût. There is virtually a very large range of dates can be processed, e.g. AD 1000.01.01. to AD .12.31. and BC 1000.01.01. to BC .12.31. Hold on: "BC 1000.01.01. to BC .12.31." makes no sense, as your start date is after your end date! Do you mean BC .12.31. to BC 1000.01.01.? But that starts at the end if the first year and finishes at the beginning of the end year, losing all but one day each of those two years. So perhaps you mean BC .01.01. to BC 1000.12.31.? That's better, but it still leaves you with what would be a roughly twenty-thousand year range - but with a strange central gap of 1998 years, from 999 BC (BCE) to AD 999 (CE) inclusive. I can't imagine you mean that. This .MM.DD. format is a beautiful workaround ... Spreadsheets have always been able to handle text. I suspect most spreadsheet users would not see selecting text as a data type to be a workaround, beautiful or otherwise. I can think of two limitations. People make mistakes. One obvious limitation is that non-existent dates can be entered as easily as real ones. Entering "2015.02.29." as text creates something looking as much like a date as does "2015.02.28.", whereas entering these (supposed) dates normally shows one as a right-aligned date and the other as left-aligned text. Only Erich Kästner and perhaps the Tiananmen Square protestors are allowed the 35th of May. But I say again: chacun à son goût. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
Hello and happy new year! At last, I knew how my colleagues strongly believed and what they wanted to achieve. They wanted a date format which works across all known free, paid, old and new versions of spreadsheet program, e.g. LibreOffice Calc. They decided to use .MM.DD. and MM.DD., e.g. 2015.01.01. ---> three dots. 1st January, 2015. and 01.01. ---> two dots. 1st January. This format is standardized internally for communication. The good things are: (1) The date is stored as "text" or "string" on the computer. It is not a "number" to the computer any more. Adding or subtracting dates is disabled. It is good for just displaying the dates, which my colleagues wanted. (2) Because of (1), the date is not converted to 01/01/2015 or 2015-01-01 or 01-01-2015 as these can be default formats. Such conversion is not welcomed because the input format is different from the displayed format. The user inputs .MM.DD. and the computer displays exactly the same format. This is the consistency my colleagues were desparately looking for. (3) No additional configuration is necessary when using this .MM.DD. format. No time is spent on training staff to format a date to a certain form in a spreadsheet program. Then this is efficient. I simply sent a notice to all staff to follow the format on behalf of my boss. (4)This format is good for sorting in the spreadsheet program. It is also used in naming files. File names can be sort accordingly. (5) There is virtually a very large range of dates can be processed, e.g. AD 1000.01.01. to AD .12.31. and BC1000.01.01. to BC .12.31. (if extended to Y.MM.DD., the range will even be larger.) because the date is not stored as a "number" and this is not limited to the design of the software (old and new). Constrained hardware can also process many dates in this format. This .MM.DD. format is a beautiful workaround when only using it to display and sort dates. I can think of two limitations. Firstly, there is no way to add or subtract dates as easily as before. (No problem because in my case, I only display and sort the dates, not adding or subtracting.) Secondly, the file size may be a bit or much larger because the "text" or "string" is stored. (No problem because of cheap hard disks.) If your case is similar to my case, I welcome your use of this .MM.DD. format. Best wishes,C. H. D. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
Hi :) Sounds like a good fail-safe, so that users are not bound by rigid restrictions but the displayed figures are always consistently correct. I suspect that you can even enter much shorter numbers for dates within the year, such as; 12.31 getting corrected to; 2014.12.31 I'm not sure if you can also drop the month, if it's within the same month, and just type 31 to get the same result but even a little less typing can make a difference. Regards from Tom :) On 28 December 2014 at 04:43, Brian Barker wrote: > At 02:29 28/12/2014 +, Conly Honly Donly wrote: > >> A quick recap: I was looking for the .MM.DD format. The user inputs >> it as it looks, e.g. 2015.01.01. (LibreOffice Calc should recognise it as a >> date.) >> Here: >> Tools -> Options -> Language Settings -> Languages -> Date acceptance >> patterns >> I don't know how this should be set. I tried .MM.DD. This did not >> work. (with the semicolon) I tried Y.M.D. This worked. (with the semicolon) >> Here: >> Format -> Cells -> Numbers -> Format code >> I must use .MM.DD, not Y.M.D. >> >> What led to this inconsistent format? I am interested in the technical >> reasons behind. I think that it will be consistent if a user is allowed to >> type the same .MM.DD format code in both places to get what she or he >> wants. >> > > I'm guessing here, but surely there are different requirements in the two > places? In the cell formatting, you are indicating exactly the format you > require - so you are choosing the year to appear as , not YY, for > example. But the acceptance pattern is more general: you are merely showing > that you want year-dot-month-dot-day to be a format automatically > interpreted as a date. With Y.M.D as an acceptance pattern, can you not > enter the forthcoming New Year's Day (for example) as 2015.01.01, 2015.1.1, > 15.01.01, and many other forms - but have all interpreted correctly and > displayed in the cell itself in the cell's format - as 2015.01.01 in your > case? > > I trust this helps. > > Brian Barker > > > > -- > To unsubscribe e-mail to: users+unsubscr...@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 > > -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
At 02:29 28/12/2014 +, Conly Honly Donly wrote: A quick recap: I was looking for the .MM.DD format. The user inputs it as it looks, e.g. 2015.01.01. (LibreOffice Calc should recognise it as a date.) Here: Tools -> Options -> Language Settings -> Languages -> Date acceptance patterns I don't know how this should be set. I tried .MM.DD. This did not work. (with the semicolon) I tried Y.M.D. This worked. (with the semicolon) Here: Format -> Cells -> Numbers -> Format code I must use .MM.DD, not Y.M.D. What led to this inconsistent format? I am interested in the technical reasons behind. I think that it will be consistent if a user is allowed to type the same .MM.DD format code in both places to get what she or he wants. I'm guessing here, but surely there are different requirements in the two places? In the cell formatting, you are indicating exactly the format you require - so you are choosing the year to appear as , not YY, for example. But the acceptance pattern is more general: you are merely showing that you want year-dot-month-dot-day to be a format automatically interpreted as a date. With Y.M.D as an acceptance pattern, can you not enter the forthcoming New Year's Day (for example) as 2015.01.01, 2015.1.1, 15.01.01, and many other forms - but have all interpreted correctly and displayed in the cell itself in the cell's format - as 2015.01.01 in your case? I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
Hello all, Thanks for following up. A quick recap: I was looking for the .MM.DD format. The user inputs it as it looks, e.g. 2015.01.01. (LibreOffice Calc should recognise it as a date.) Here: Tools -> Options -> Language Settings -> Languages -> Date acceptance patterns I don't know how this should be set. I tried .MM.DD. This did not work. (with the semicolon) I tried Y.M.D. This worked. (with the semicolon) Here: Format -> Cells -> Numbers -> Format code I must use .MM.DD, not Y.M.D. What led to this inconsistent format? I am interested in the technical reasons behind. I think that it will be consistent if a user is allowed to type the same .MM.DD format code in both places to get what she or he wants. Best wishes, C. H. D. _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
At 15:27 19/12/2014 +, Cony Honly Donly wrote: When I enter 2015.01.01, it is recognized not as a date. How do I make it one step only? Aha! This is a different question: you need Calc to recognise your entered text as a date, to convert the text string to the internal number representing that date (that's probably 42005 in your example case), and to set the cell's format to a date format if you have not already done this. There are two solutions to this: o As has already been suggested, if this format is not accepted by default in your locale, you may be able to add it at Tools | Options... | Language Settings | Languages | Language of | Date acceptance patterns. o Failing this, you could easily set up a formula. Enter your desired text into, say, column A. In B1, say, enter something like =DATE(LEFT(A1;4);MID(A1;6;2);RIGHT(A1;2)) and fill this down the column. If you want to be able to read column B, you will need to format it with your desired format. But you may be happy reading column A instead. Any formulae for calculations depending on your input would depend on column B, so would work as soon as you had entered your text into A. So this technique passes your "one step" test. o You might find that =DATEVALUE(A1) works as well, but I imagine that this will work only with date formats that follow one of the acceptance patterns anyway. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
Hi :) If the formatting is set to display the date in the way that you want then when you type in; 19/12 then it should display as 19.12.2014 (or whatever your formatting is for dates). So if you have a whole column, or row of dates then it's not really a 2 step process, more like (n+1)/n steps as the initial step set-up lots of cells all at once. Sorry that is not a huge help! It's an awkward work-around. You really kinda need to be able to type in roughly the way it is meant to look. Regards from Tom :) On 19 December 2014 at 15:27, C. H. D. wrote: > > Hello all, > When I enter 2015.01.01, it is recognized not as a date. How do I make it > one step only? I enter it and it is detected as a date. > > 01-01-2015 is recognized as a date. Then I need to format this. Two steps > are required. > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ > > -- > To unsubscribe e-mail to: users+unsubscr...@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 > -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
On 19 December 2014 at 08:47, C. H. D. wrote: > In a spreadsheet, I need the dot separated date in .MM.DD format, e.g. > 2015.01.012015.12.11 I suspect you would have more success using the ISO format of -MM-DD instead of a non-standard format. -- T. R. Valentine A rich heart may be under a poor coat. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
Hi all: I think you need to use this format: /MM/DD Regards, Jorge Rodríguez El vie, 19-12-2014 a las 14:47 +, C. H. D. escribió: > Hello all, > In a spreadsheet, I need the dot separated date in .MM.DD format, e.g. > 2015.01.012015.12.11 > Should I file a bug report to add this feature? Is it currently not available? > > Please advise. Thanks. > > C. H. D. > > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ > -- Atentamente, Jorge Rodríguez -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
I tried this, which seem to work: specifying the cell format with a custom format .MM.DD. When I input a date in this form, it is kept this way, and is recognized as a date (possible to use it in formula). Is that not enough? Maybe I misunderstood the question. -- Cley Faye http://cleyfaye.net 2014-12-19 16:27 GMT+01:00 C. H. D. : > Hello all, > When I enter 2015.01.01, it is recognized not as a date. How do I make it > one step only? I enter it and it is detected as a date. > > 01-01-2015 is recognized as a date. Then I need to format this. Two steps > are required. > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ > > -- > To unsubscribe e-mail to: users+unsubscr...@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 > -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
Hello all, When I enter 2015.01.01, it is recognized not as a date. How do I make it one step only? I enter it and it is detected as a date. 01-01-2015 is recognized as a date. Then I need to format this. Two steps are required. _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
At 14:47 19/12/2014 +, Conly Honly Donly wrote: In a spreadsheet, I need the dot separated date in .MM.DD format, e.g. 2015.01.012015.12.11 In the Format Cells dialogue, you can create your own "User-defined" formats. Just enter your ".MM.DD" in the "Format code" box there. Some formats get complicated by locale-specific properties, but I doubt there is any problem with this one. Alternatively, you could always create a formula to develop from a genuine date value a text string (in a separate cell) representing any format you wanted. Should I file a bug report to add this feature? Probably not. Is it currently not available? It is - as above. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
On Fri, 19 Dec 2014 14:47:02 + (UTC) "C. H. D." wrote: Hello C., >In a spreadsheet, I need the dot separated date in .MM.DD format, >e.g. 2015.01.012015.12.11 Go to Format Menu, Cells... then select Numbers tab -> Time. Enter time in the format you require in the entry box. >Should I file a bug report to add this feature? Is it currently not >available? No bug report required. Not all options are offered "out of the box"; There are simply way too many permutations. -- Regards _ / ) "The blindingly obvious is / _)radnever immediately apparent" Bet you thought you had it all worked out Problem - Sex Pistols -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Dot separated date in YYYY.MM.DD format
I think it is already available. You have to change the cell format (date type) in .MM.DD, nothing more Federico Quadri "C. H. D." ha scritto: Hello all, In a spreadsheet, I need the dot separated date in .MM.DD format, e.g. 2015.01.012015.12.11 Should I file a bug report to add this feature? Is it currently not available? Please advise. Thanks. C. H. D. -- To unsubscribe e-mail to: users+unsubscr...@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
