[Solved] Format function and dates
-
- Posts: 25
- Joined: Thu Dec 06, 2007 1:17 pm
[Solved] Format function and dates
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?
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.
Reason: tagged the thread as Solved.
Re: Format function and dates
Reading this:Hacker John wrote:Does it not work in ooBasic or am I doing something wrong?
- 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
Re: Format function and dates
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").
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 25
- Joined: Thu Dec 06, 2007 1:17 pm
Re: Format function and dates
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.
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.
-
- Volunteer
- Posts: 1160
- Joined: Mon Oct 08, 2007 1:26 am
- Location: France, Paris area
Re: Format function and dates
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.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?
Check you have a recent OpenOffice and a correct value in Tools > Options > Language settings > Languages > Locale setting
______
Bernard
Re: Format function and dates
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
Re: Format function and dates
Sorry, does not work with more than one language, although the last boolean argument is supposed to allow English format strings.
I tested:
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.
I tested:
Code: Select all
...
oLocale.Language = "de"
...
msgbox oNumberFormatter.convertNumberToPreviewString ("DD MMMM YYYY, HH:MM.SS", d, oLocale, true)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 25
- Joined: Thu Dec 06, 2007 1:17 pm
Re: Format function and dates
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.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
-
- Posts: 25
- Joined: Thu Dec 06, 2007 1:17 pm
Re: Format function and dates
Thanks for that code - It works a treat!Just for completeness here a code snippet, which uses the UNO API. It is slightly overkill, but allows for full control including the locale.