Problem description: i got an error in Libreoffice Calc. when i try to get Date Different from two cell with this cell content type (DD/MM/YYYY). Datedif() function only works with this type(MM/DD/YYYY). Steps to reproduce: 1. on on cell put this date (19/05/2012) with this (DD/MM/YYYY) date format. 2. on other cell enter another date like (25/05/2012) with this (DD/MM/YYYY) date format. 3. datedif(Cell1,Cell2,"d"); 4. you should got Error :-S Current behavior: Expected behavior: Operating System: All Version: 4.1.1.2 release
Not reproducible for me with LibreOffice 4.1 nor the master. Please, what is your settings under menu Tools > Options > Language Settings > Languages -> Date acceptance patterns ? Do you have D/M/Y ? If not you should add it if you want use it. Best regards. JBF
Also might want to update to latest LO release, currently 4.1.3.2: http://www.libreoffice.org/download/
I can replicate in Version: 4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a under Windows. I have also tested before and after adding additional DATE formats to the language options, but the behavior remains the same. My main language setting in Calc is English (USA). I noticed that when formatting the cells as MM/DD/YYYY, the inserted date is aligned to the right; whereas in cells formatted as DD/MM/YYY, the inserted dates are aligned to the left. The behavior also remains the same whether I format the cells first before inserting the date values, or reverting the order of my actions. Although I am not the OP, since I have the DD/MM/YYYY language setting mentioned in Comment #1, I am changing the status from NEEDINFO to UNCONFIRMED. I am not setting this to NEW yet because others cannot reproduce. Regards, Ady.
Setting OS to windows.
Version: 4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a on Windows. The following information might require its own separate bug report(s), but I think it might be useful for this bug too. Using other date-related functions (besides DATEDIF), they _all_ fail, except when the referenced cell is formatted with my default locale setting in Calc. I have added (for testing) these "Date acceptance patterns": M/D/Y;M/D;YYYYMMMDD;D/M/Y;DD/MM/YYYY but only when using my default English (USA) "MM/DD/YYYY" I get to see a result. For example, using the YEAR function, all other formatted cells return #VALUE. Moreover, I tried using "CELL(FORMAT,A1)", and this CELL function fails with #NAME every time (no matter which format the referenced cell A1 is using. Any variation of CELL() (e.g. CELL(CONTENTS,A1) and so on) gives me the same #NAME result, so probably this is an additional separate bug. Since the alignment is also different when using MM/DD/YYY than when using other non-default date formats (such as DD/MM/YYYY in my case), it seems to me that the bug is not really in the DATEDIF function, but in the way Calc is identifying those cells (seemingly, as 'text'). Regards, Ady.
(In reply to comment #5) > Version: 4.1.3.2 > Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a > on Windows. > > The following information might require its own separate bug report(s), but > I think it might be useful for this bug too. > > Using other date-related functions (besides DATEDIF), they _all_ fail, > except when the referenced cell is formatted with my default locale setting > in Calc. I have added (for testing) these "Date acceptance patterns": > M/D/Y;M/D;YYYYMMMDD;D/M/Y;DD/MM/YYYY > but only when using my default English (USA) "MM/DD/YYYY" I get to see a > result. For example, using the YEAR function, all other formatted cells > return #VALUE. > > Moreover, I tried using "CELL(FORMAT,A1)", and this CELL function fails with > #NAME every time (no matter which format the referenced cell A1 is using. > Any variation of CELL() (e.g. CELL(CONTENTS,A1) and so on) gives me the same > #NAME result, so probably this is an additional separate bug. > > Since the alignment is also different when using MM/DD/YYY than when using > other non-default date formats (such as DD/MM/YYYY in my case), it seems to > me that the bug is not really in the DATEDIF function, but in the way Calc > is identifying those cells (seemingly, as 'text'). > > Regards, > Ady. In addition, if I first insert a date as MM/DD/YYYY and *then* I change it to DD/MM/YYYY formatting, the cell is correctly interpreted as a date. If I first format a cell as DD/MM/YYYY, and *then* a date is inserted with such format, the date is treated as 'text' and not as 'date' (say, 18/11/2013), but if I insert the corresponding value, 41596, the cell is correctly shown as 18/11/2013 with the expected alignment and functions such as YEAR work as expected too. BTW, I am having similar experiences (cells treated as 'text') with other number formats, not just with dates. Regards, Ady.
You just correctly observed that a number format such as a date format is a DISPLAY format, not a data entry mask. Date input is evaluated according to the date acceptance patterns that can be viewed and changed under Tools -> Options -> LanguageSettings -> Languages.
(In reply to comment #7) > You just correctly observed that a number format such as a date format is a > DISPLAY format, not a data entry mask. Date input is evaluated according to > the date acceptance patterns that can be viewed and changed under Tools -> > Options -> LanguageSettings -> Languages. My tests are showing that the additional "Date acceptance patterns" are not working as expected (a.k.a BUG). The "Date acceptance patterns" is supposed to help the user introduce dates using alternative date formats (other than its default in Calc Language settings). I can insert the date in my default format and then change the format of the cell as I want; but then there is no point for the "Date acceptance patterns". The whole point of "Date acceptance patterns" is for the user to be able to insert _other_ date formats and Calc should identify (format) it as date "automatically". Hopefully I am being more clear now. Perhaps this incorrect behavior is more relevant for bug #71664, as it is not a problem exclusive to DATEDIF. Regards, Ady.