Hello,

Thank you for filing the bug. Please send us a sample document, as this makes it easier for us to verify the bug. 
I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document.
(Please note that the attachment will be public, remove any sensitive information before attaching it.)
How can I eliminate confidential data from a sample document?
https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F
Thank you

Created attachment 124160 [details]
Calc file with macro that shows the bug

Created attachment 124161 [details]
Messagebox of macro that show the bug.

Uploaded a sample document and an image with the result of the macro showBug. I hope this will give you enough information.

Your format is incorrect.
minutes use format descriptor : mm

(month uses also format descriptor : MM or mm)

See help for Calc : formatting of cells, numeric format.

Hello Bernard,

Your solution (Format(Now, "yyyymmdd\_hhmmss")) does work, but it is plain wrong behaviour. First time mm is replaced by the month and later the same mm is replaced by something else: minutes. What if I want to format a string in the form of years and minutes. I can do that in all kind of languages, including VBA. In Calc basic, it does go wrong. In every other language a distinct character is used for minutes. And we are not talking about cell formatting, but about the Basic scripting language. But I think it is strange for cell formatting too.
Also Calc Basic is in VBA compatibility mode (via switch Option VBASupport 1). The documentation of VBA states:
https://msdn.microsoft.com/en-us/library/office/gg251755.aspx
nn, NN, Nn does work fine in Excel.


Regards,

Roy

I can confirm with Version: 5.2.0.0.alpha1+; win7
In excel nn show minutes, in Calc it show first two letters 

Option VBASupport 1 - VBA compatibility

With this simple function test:

sub TestFormat
print format(Now,"YYYY-MM-DD MM ")
End Sub

The result is the month with MM. But if we add or precede with MM and HH or SS, the result is as expected of minutes.

The story begins with the RTL RTL function FUNC (Format) (3498 line in methods.cxx). It follows a call to the function 'format' with the format string and the number or string to format. Beginning at line 661 (in sbxscan.cxx), some basic treatments are done on simple format string, and there, n or nn (N or NN) is correctly treated as a standalone simple format string (i.e. nothing else then "n" or "nn"). More complex format strings treatment are deferred to SvNumberFormatter where code "n" or "nn" is not take into account as expected.

Here is a follow-up

The problem occurs before calling ImpGetDateTimeOutput (in /svl/Sources/source/number/zformat.cxx, line 3499). The format "yyyymmdd \ _hhnnss" is already decoded. Each of the codes is assigned an index value that allows the switch structure (switch (rInfo.nTypeArray [i]) line 3530) in the loop (for (sal_uInt16 i = 0; i <nAnz; i ++) line 3528) To build the final result in sBuff. The Order of execution of the "Case" is as follows into ImpGetDateTimeOutput. Anz 

Anz=7
0: yyyy	case NF_KEY_YYYY			sBuff ="2016"
1: mm	case NF_KEY_MM				sBuff ="201612"
2: dd	case NF_KEY_DD				sBuff ="20161212"
3: \_	case NF_SYMBOLTYPE_TIME100SECSEP:	sBuff ="20161212_"
4: hh	case NF_KEY_HH:				sBuff ="20161212_19"
5: nn	case NF_KEY_AAA: (short Day Name)	sBuff ="20161212_19lun."
6: ss	case NF_KEY_SS: 			sBuff ="20161212_19lun.14"

BASIC code sample was:
sub TestFormat2
	Print Format(Now, "yyyymmdd\_hhnnss")
End Sub

103/5000
The structure rInfo seems to contain the list of codes (integer values) translating the literal format.

The translation of the format into code for the rInfo structure seems to come from method aFormatter.PutandConvertEntry(aFmtStr, nCheckPos, nType, nIndex, LANGUAGE_ENGLISH, eLangType). I'm here!

Note : LibreOffice
Version: 5.3.0.0.alpha1+
Build ID: 47f74a96e4fee0d834a7421c231d0018ecd07c6f

Created attachment 129678 [details]
Date/Time Format Codes in LO Source Code

 The source of the problem comes from source code LO which interprets the code NN (nn) as the abbreviation of the day whereas VBA interprets the code NN like minutes.

Codes definition in LO are:
  sKeyword[NF_KEY_M] =     "M";        // month 1
  sKeyword[NF_KEY_MM] =    "MM";       // month 01
  sKeyword[NF_KEY_NN] =    "NN";       // Day of week short
  sKeyword[NF_KEY_NNN] =   "NNN";      // Day of week long

In the BASIC machine we have the following definition when the format code is alone. In particular, when the NN code is not embedded in the year and day codes, in which case the decoding of LO applies.

#define VBAFORMAT_N                 "n"
#define VBAFORMAT_NN                "nn"

Where n and nn are interpreted like minutes.

So we have to decide here the desired behavior of the BASIC machine when the N or NN code is in the string of a format. Do we stick to LO and then the only way to get the minutes is to use the M or MM code? In this case, we will have a possible collision with the code for the months. Or should we look for compatibility with VBA? In the latter case, you can change the behavior of LO using the VBA compatibility option. What do you think?

It seemed possible to me to code an exception to obtain the desirable behavior (compatibility with VBA) of the codes "N" and "NN". But, I do not like this approach. Especially since LO's special use of the "N" and "NN" codes does not seem useful to me and could be abandoned. I have attached a Calc file that reproduces the DATE / Hour codes as encoded in the /libreoffice/svl/source/numbers/zforscan.cxx file. I highlighted the problem codes related to the bug.

So, I repeat my question, what do you think?

Impacts of codes "N" and "NN" outside BASIC

1. In method OutputEndNode, lines 4066-4068 (wrtwww8.cxx)
2. In method lcl_IsDefaultDateFormat, lines 1008-1010 (xmlnumfe.cxx)
3. In method ExportPart_Impl, lines 1587-1589 (xmlnumfe.cxx)
4. In method EndElement, lines 1149, 1247 (xmlnumfi.cxx)
5. In method AddNfKeyword, lines 2117, 2119

Here are the possible solutions.

1. Codify an exception to get VBA compatibility when the "N" and "NN" codes are inserted into a time format string. This solution has the least impact on the modules of the LibreOffice suite.
2. Redefine the codes "N" and "NN" in favor of a behavior identical to that of VBA. This solution makes it necessary to modify the code of LibreOffice wherever the codes "N" and "NN" are interpreted.

Currently, the codes "N" and "NN" respectively represented by the keys NF_KEY_N and NF_KEY_NN are used in LO only for the abbreviation format of the name of the day (Mon, Tue, Wed, Thur., ...) and the long name of the day (Monday, Tuesday, Wednesday, Thursday, ...). Now the keys NF_KEY_DDD and NF_KEY_DDD already fulfill the need for the abbreviation format of the name of the day and format of the long name of the day. Moreover, StarBasic code "N" and "NN" as formats different from those of LO and this for a very particular situation and not too useful! There is a blatant inconsistency and confusion in the use of the N, NN, DDD and DDDD codes.

The first solution appears as a crutch. Coding for an exception will not resolve the source of confusion. Nevertheless, if this solution were to be implemented, the algorithm would have to distinguish between two situations of the codes: a) the codes are embedded in a time format string in which case the interpretation of the N and NN codes would be in accordance with VBA, that is to say that N would code for minutes without 0 in prefix and NN would code for minutes with 0 in prefix; B) otherwise the codes would be interpreted as they are currently, ie N would code for the abbreviation of the day name and NN would code for the format of the long name of the day.

The second solution appears more robust. The confusion would be definitively eliminated. It remains that the historical reason behind the choice of letters N and NN to codify a short and long day format is unknown to me. It is therefore possible that old files based on the current interpretation of the N and NN codes require corrections to adapt to the DDD and DDDD codes.

I will code for the second solution!

(In reply to Pierre Lepage from comment #12)
> Impacts of codes "N" and "NN" outside BASIC
> 
> 1. In method OutputEndNode, lines 4066-4068 (wrtwww8.cxx)
> 2. In method lcl_IsDefaultDateFormat, lines 1008-1010 (xmlnumfe.cxx)
> 3. In method ExportPart_Impl, lines 1587-1589 (xmlnumfe.cxx)
> 4. In method EndElement, lines 1149, 1247 (xmlnumfi.cxx)
> 5. In method AddNfKeyword, lines 2117, 2119 (xmlnumfi.cxx)

Also
6. In FillkeywordTableForExcel, lines 786-788 (zforlist.cxx)

To clarify: the 'n' and 'nn' codes are VBA specific, they are not recognized by Excel number formatting, so changing the number formatter implementation for this is not appropriate.

Dear developer,
This bug has been in ASSIGNED status for more than 3 months without any activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.

Dear Roy D,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug