Formatting a calc cell as a date in a basic macro

[ztoddw]

Getting error- see comments in my code I pasted for where I get the error… If it’s because I need a different format string, what format string should I use to format the cell as a date?

function test9
	Dim Doc As Object
	Dim Sheet As Object
	Dim Cell As Object
	Dim NumberFormats As Object
	Dim NumberFormatString As String
	Dim NumberFormatId As Long
	Dim LocalSettings As New com.sun.star.lang.Locale
	
	Doc = ThisComponent
	Sheet = Doc.Sheets.getByName("Details by CSR")
	msgbox sheet.name
	Cell = Sheet.getCellByPosition(0,0)
	
	Cell.Value = 44819 '"9/1/2022"
	
	LocalSettings.Language = "en"
	LocalSettings.Country = "us"
	
	NumberFormats = Doc.NumberFormats
	NumberFormatString = "M/dd/yyyy"			'What should I use to format as a date? Tried lots of things.
	
	NumberFormatId = NumberFormats.queryKey(NumberFormatString, LocalSettings, True)
	If NumberFormatId = -1 Then
		'Next line gives RunTime error '1' : RunTimException
	  NumberFormatId = NumberFormats.addNew(NumberFormatString, LocalSettings)
	End If
	
	MsgBox NumberFormatId
	Cell.NumberFormat = NumberFormatId
End Function

[ztoddw]

Found the answer- I needed to use captial letters in the formatting. (M/DD/YYYY)

Amazing how hard it was to find this out. Couldn’t find info on format strings anywhere. Anyone know where that info is?

Is there a way to use the Doc.NumberFormats object in my code to show the format string for a particular number format ID?

[Lupp]

Do you mean something like

nfObj  = ThisComponent.NumberFormats.getByKey(myCell.NumberFormat)
nfCode = nfObj.FormatString

BTW: The ONLY reasonable date format in our context is ISO 8601 extended :
YYYY-MM-DD Everything else may be used in historical context if urgently needed - or for joking.

[erAck]

@ztoddw

Couldn’t find info on format strings anywhere. Anyone know where that info is?

See online help Number Format Codes .
As of LO 7.4 using lower case keywords with queryKey() should work as well, see tdf#52602.
Btw, the correct ISO 3166 country code is upper case US but that doesn’t matter here.

[Lupp]

@ztoddw

I needed to use captial letters in the formatting.

I also don’t know a specification to that effect. A background may be that there are many scripting systems in the world not knowing the concept of letter-case. On a respective keyboard it may be complicated to enter “Western” letters, and even more to make the case distinction. (In fact also the ancient Greeks and Romans only invented the “lower case” after a long time of writing the original letters -true GLYPHS- more and more disfigured -handy- by hand. As I see it, lower case is an accepted and standardized way of sloppiness.)
You can easily see the effect without relying on user code: Enter a Format Code via the UI dialog using lower case for reserved characters, OK the entry, and open the dialog again. Automatuic case conversion is manifest.
A more technical reason may be that international standards go back to times when also the not-ancient Westerners only used 32 code places in IT-based communication (teletype). ISO standards may (sensibly) still regard the principle of very few, but unmistakable code places. (Even unicode may not live forever?)
Personally I gravely dislike the ambiguous usage of “M”.

[erAck]

The reason that LibreOffice (and former OOo) internally stores format codes with capital letters is simply that the codes are case insignificant but processed and stored in common upper casing for easier retrieval and matching. The XNumberFormats::queryKey() API function added later did not take that into account.