[Solved] Format function and dates

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Hacker John
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm

[Solved] Format function and dates

Post by Hacker John »

The following code

dim s as date, a as string

s = DateValue(Date())

a = Format(s, "MMMM")
msgbox a

Just gives me e.g. '28/02/2008" rather than "February. I can't seem to make Format() work with dates? Does it not work in ooBasic or am I doing something wrong?
Last edited by Hagar Delest on Tue Jun 10, 2008 2:01 pm, edited 2 times in total.
Reason: tagged the thread as Solved.
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: Format function and dates

Post by hol.sten »

Hacker John wrote:Does it not work in ooBasic or am I doing something wrong?
Reading this:
- Formatting Strings: http://docs.sun.com/app/docs/doc/819-04 ... exterm-112
- Date and Time: http://docs.sun.com/app/docs/doc/819-04 ... =en&a=view
it seems that "a = Format(s, "MMMM")" does not work with OOo Basic. Looks like you need something like this:

Code: Select all

Select Case Month(MyDate)
case 1
   MyWeekday = "January"
case 2
...
End Select
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Format function and dates

Post by Villeroy »

You can use spreadsheet functions for anything:
The following calls a wrapper function getSheetFunctionResult 2 times. First it gets a number from a given date string, then it gets the formatted string by that value and a format string.
Same as spreadsheet formula =TEXT(VALUE(A1);B1) where A1 has a date-string ("2000-12-31") and B1 a format-string ("TT MMM YYYY").

Code: Select all

Function getDateString(strValue, strFormat)
oSrv = createUnoService("com.sun.star.sheet.FunctionAccess")
n = getSheetFunctionResult(oFA, sName$, optional args)
getDateString = getSheetFunctionResult(oFA, "TEXT", Array(n, strFormat))
End Function

Code: Select all

REM oFA as service c.s.s.sheet.FunctionAccess
REM sName is the english function name (e.g. "MATCH")
REM args: missing (e.g. with sName "NOW"),
REM single arguments as string, as double,
REM as range object or a DataArray(cols(rows()))
REM multiple arguments as array
REM oFA, "MATCH", Array( 23, DataArray(cols(rows())), 0 )
REM oFA, "MATCH", Array( 23, oRangeObj, 0 )
Function getSheetFunctionResult(oFA, sName$, optional args)
on error goto nullErr:
	if isMissing(args) then
		args = Array()
	elseif NOT isArray(args) then
		args() = Array(args)
	endif
	getSheetFunctionResult = oFA.callFunction(sName, args())
exit function
nullErr:
	getSheetFunctionResult = Null
End Function

Code: Select all


Just for the records. We do not use this adjusted FunctionAccess in the above example.
REM return a service for function access with the same properties as given
REM spreadsheet-document, e.g. MatchWholeCell, Regex, NullDate,...
REM We get a plain FunctionAccess for non-spreadsheets
Function getFunctionAccessByDocument(oDoc)
Dim oSrv, aProps(), i%, sName$, v
	oSrv = createUnoService("com.sun.star.sheet.FunctionAccess")
	if oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		aProps() = oSrv.PropertySetInfo.getProperties()
		for i = 0 to uBound(aProps())
			sName = aProps(i).Name
			v = oDoc.getPropertyValue(sName)
			oSrv.setPropertyValue(sName, v)
		next
	endif
getFunctionAccessByDocument = oSrv
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Hacker John
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm

Re: Format function and dates

Post by Hacker John »

Thanks Viileroy, for clearing that up, and some very comprehensive code. I've cretated a database report via the wizard and have been hacking it about to customise it. I'd stuck a label control at the top and was trying to use it to display the month and year as eg "April 2008".

Since the database is for my accounts, and one of the query fields that feeds the report is a transaction date, I'm going to place a formatted field, link it to the date field and set it's format accordingly. I shall grab your spreadhseet functions however as I am certain I'll need them at some stage.

Thanks again.
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Format function and dates

Post by B Marcelly »

Hacker John wrote:The following code

dim s as date, a as string

s = DateValue(Date())

a = Format(s, "MMMM")
msgbox a

Just gives me e.g. '28/02/2008" rather than "February. I can't seem to make Format() work with dates? Does it not work in ooBasic or am I doing something wrong?
This code works for me. I get the name of the month. Tested on OOo 2.4.0 RC2, in french and in english US.
Check you have a recent OpenOffice and a correct value in Tools > Options > Language settings > Languages > Locale setting

______
Bernard
ms777
Volunteer
Posts: 176
Joined: Mon Oct 08, 2007 1:33 am

Re: Format function and dates

Post by ms777 »

Just for completeness here a code snippet, which uses the UNO API. It is slightly overkill, but allows for full control including the locale.

Code: Select all

Sub Main 

d = CDate(38541.173) 

oNumberFormatter = createUnoService("com.sun.star.util.NumberFormatter") 
oNumberFormatter.attachNumberFormatsSupplier(ThisComponent) 

oLocale  = createUnoStruct( "com.sun.star.lang.Locale" ) 
oLocale.Language = "en" 

msgbox oNumberFormatter.convertNumberToPreviewString ( "DD:MM:YYYY", d, oLocale, true ) 
msgbox oNumberFormatter.convertNumberToPreviewString ( "DD MMM YY", d, oLocale, true ) 
msgbox oNumberFormatter.convertNumberToPreviewString ( "DD MMM YYYY, HH:MM.SS", d, oLocale, true ) 

end sub
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Format function and dates

Post by Villeroy »

Sorry, does not work with more than one language, although the last boolean argument is supposed to allow English format strings.
I tested:

Code: Select all

...
    oLocale.Language = "de"
...
msgbox oNumberFormatter.convertNumberToPreviewString ("DD MMMM YYYY, HH:MM.SS", d, oLocale, true)
which yields "DD Juli YYYY, 04:09.07. I've got to adjust the format string to the desired output-locale (German: "TT MMMM JJJJ, HH:MM.07").
Calc's TEXT seems to work with English format strings.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Hacker John
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm

Re: Format function and dates

Post by Hacker John »

This code works for me. I get the name of the month. Tested on OOo 2.4.0 RC2, in french and in english US.
Check you have a recent OpenOffice and a correct value in Tools > Options > Language settings > Languages > Locale setting
I've got 2.3.1. When I go to the OO site, I can't see 2.4.0 for download. Didn't think it was released yet.
Hacker John
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm

Re: Format function and dates

Post by Hacker John »

Just for completeness here a code snippet, which uses the UNO API. It is slightly overkill, but allows for full control including the locale.
Thanks for that code - It works a treat!
Post Reply