Description: When you define date format you expect : 1- To store the content as a date (if content set is not valid - not recognized - content is left centered to tell that the content is read as standard text) 2- to be able to calculate from the date 3- convert to any format 4- increment the date when cell is multi-duplicated (don't know the right expression in English). This according to format There is strong bug but difficulties to perform operations regard to excel What happens : 1- hits of not exact format of dates are not recognized (for standard and user formats). For example and particularly : - month formats : with format defined JJ/MM/AAAA - unable to understand MM/AAAA (supposed 01/MM/AAA), - unable to recognize a wrong like decimal point (in French keyboard the dot of numerical keypad is translated to "," - unable to (recognize a "janv.2020" as "jan. 2020") recognize without error needs to read with format "MMM. AAAA" MMM = "jan" and AAAA="2020" - many examples hold by excel which make easier to get the dates from keyboard when they are not exactly fitting the format - ... 2- Increment when duplicating (multi-copy by dragging copy point) a cell with date function without taking in account the format, for example if format does not display the JJ it could be supposed that the date is incremented by "month" or year if "M" is not included in format ("A" format for example would lead to "01/01/A"). Then to increment by copy a (complex for some users) formula must be developed ("=DATE(ANNEE(A3);MOIS(A3)+1;JOUR(A3))" for French version or "=DATE(YEAR(A3),MONTH(A3)+1,DAY(A3))" in English) to get a calendar with months (date associated to a month is supposed to be 01/MM (MM/01 in English) Note : I had to produce a two columns calendar with : - months (note that when user defines a month it most of time not a date but a span of time 01/MM to endOfMonth/MM). Truncate the JJ converts a date to a SPAN of time of one month - a fixed JJ into the month ( a date to pay) This is currently a major difficulty for most of users. As often libreoffice is very rigorous, but this lead that the incremented value of "jan. 2020" is shown as "jan. 2020" while the "internal" date which is not visible is incremented of one day... (What happens with date with time) To implement a clear increment would need to define the formula if incremented element is not visible into the format Steps to Reproduce: 1. no matter 2. 3. Actual Results: see extended description Expected Results: see extended description Reproducible: Always User Profile Reset: No Additional Info: see extended description Note : While I was writing this report I could not restart to execute the update to 6.1.3 previously used. I suppose that the concerned function has not been updated from 6.1.1 to 6.1.3
Nobody answer ? I confirm some troubles. It's impossible to have MMMM format, you need all the date set, 05/2001 doesn't work...so you have to use some workaround like this : =TEXTE(DATE(1;B1;11);"MMMM") with month number in B1, if you want only show the month with letter. (we can have this in another online spreadsheet by the way)