[Solved] Format function and dates

Creating a macro - Writing a Script - Using the API

[Solved] Format function and dates

Postby Hacker John » Thu Feb 28, 2008 10:36 pm

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.
Hacker John
 
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm

Re: Format function and dates

Postby hol.sten » Thu Feb 28, 2008 11:40 pm

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   Expand viewCollapse view
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
hol.sten
Volunteer
 
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: Format function and dates

Postby Villeroy » Fri Feb 29, 2008 12:48 am

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26700
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Format function and dates

Postby Hacker John » Fri Feb 29, 2008 9:48 am

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.
Hacker John
 
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm

Re: Format function and dates

Postby B Marcelly » Sat Mar 01, 2008 9:50 pm

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
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Format function and dates

Postby ms777 » Sun Mar 02, 2008 6:27 pm

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   Expand viewCollapse view
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
ms777
Volunteer
 
Posts: 131
Joined: Mon Oct 08, 2007 1:33 am

Re: Format function and dates

Postby Villeroy » Sun Mar 02, 2008 9:42 pm

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   Expand viewCollapse view
...
    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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26700
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Format function and dates

Postby Hacker John » Thu Mar 06, 2008 12:39 am

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

Postby Hacker John » Thu Mar 06, 2008 12:46 am

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!
Hacker John
 
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests