OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Problem format Date

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
firefox84
Newbie
Newbie


Joined: 22 Dec 2008
Posts: 3

PostPosted: Mon Dec 22, 2008 10:07 am    Post subject: Problem format Date Reply with quote

Hi, I have have create a macro that reads in a document the field Date, the format of date insert into a field of Calc table is GG/MM/AA, but the date reads of my macro isn't for example 22/12/08, but is 39804.

Is there a way to convert the data accessed 39804, in the format 22/12/08 ?

Thanks
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 23, 2008 2:39 am    Post subject: Reply with quote

=TEXT(A1;"GG/MM/AA")
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
johnnycashplus
Newbie
Newbie


Joined: 09 Dec 2008
Posts: 4

PostPosted: Fri Jan 02, 2009 9:46 pm    Post subject: Reply with quote

If I were to use that "Text" fix then wouldn't I have problems if you wanted to use a formula using that cell to calculate another date.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Jan 04, 2009 2:26 am    Post subject: Reply with quote

johnnycashplus wrote:
If I were to use that "Text" fix then wouldn't I have problems if you wanted to use a formula using that cell to calculate another date.

Very good question, but he wants a macro (a little progam) to read a date from a cell. If his program asks for the cell's value he gets exactly that value which, like all spreadsheet values, a decimal number. His program could query all kinds of things from a cell, but it asks for the value. He has some problem with that value without telling us which problem.
SInce he prefered to post in the Calc forum rather than the forum for this type of questions, I prefered to provide the answer how to get the string-representation of a formatted number in a Calc spreadsheet. His macro could read that one or call function TEXT internally without using a cell. The trick works in any other spreadsheet as well as long as "GG/MM/AA" is a valid format. Could be Italian notation. In English it would be "DD/MM/YY".
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
firefox84
Newbie
Newbie


Joined: 22 Dec 2008
Posts: 3

PostPosted: Mon Jan 12, 2009 1:43 am    Post subject: Reply with quote

I apologize for not having explained well, and my problem 'as follows, I have a macro that reads a cell from the data contained in it:

Eg.

Date

10/10/09
11/10/09
13/10/09

the code that makes the reading of the cell is as follows:

Dim cell as Object
Dim i as Integer
Dim valueCell as Integer


For i = 1 to nCols
oRange.getCellByPosition cell = (i - 1, index)
TxtCampo = oDlg.getControl ( "txtCampo" & i)

Select Case cella.Type
Case com.sun.star.table.CellContentType.VALUE
valueCell = cell.getFormula
or
valueCell = cell.Value
End Select

If the date is 10/10/09 valueCell read = 40096

If I re-enter through my macro data in the paper, the value entered is 40096.

Is there a way to convert the 40096 in the format 10/10/09?



I tried to read the data as a string

Eg.

Date

10/10/09
11/10/09
13/10/09

the code that makes the reading of the cell is as follows:

Dim cell as Object
Dim i as Integer
Dim valueCell as String


For i = 1 to nCols
oRange.getCellByPosition cell = (i -1, index)
txtCampo = oDlg.getControl ( "txtCampo" & i)

Select Case cella.Type
Case com.sun.star.table.CellContentType.VALUE
valueCell = cell.getString
End Select

If the date is 10/10/09 read valueCell = 10/10/09

If I re-enter through my macro data in the paper, the value added is in text format'10 / 10/09.

Code to insert date in cell:

cella.setString (txtCampo.Text)

Is there a way to avoid adding the character '?

Thanks for any replay
Back to top
View user's profile Send private message
firefox84
Newbie
Newbie


Joined: 22 Dec 2008
Posts: 3

PostPosted: Thu Jan 15, 2009 2:44 pm    Post subject: Reply with quote

In Api I found the command DateFormat: http://api.openoffice.org/docs/common/ref/com/sun/star/awt/UnoControlDateFieldModel.html # Date

through this we can change the date format, can you tell me how to use or thats command, you do an example of its use?

My goal is to change the date format in a cell, by using a my macro.

For example:

My macro reads 21/10/2008, the goal of macro must change a DateFormat into 10/21/2008.


I considerably simplify my problem,because my English is not very good.
Back to top
View user's profile Send private message
turtle47
Power User
Power User


Joined: 24 Aug 2008
Posts: 66
Location: Germany

PostPosted: Fri Jan 16, 2009 3:02 am    Post subject: Reply with quote

Hi,

to change the cellformat have a look to this code:
Code:
sub DateSub
Dim oSheet
Dim oSel
Dim str as String
nFormat = fnGetNumberFormatId(thisComponent,"MM/TT/JJ")'< German |Change for Italia > "MM/GG/AA"
oSheet = Thiscomponent.CurrentController.ActiveSheet
oSel = osheet.getCellRangebyName("A1")
mycell = oSel.string
oSel.Value= CDate(mycell)
oSel.setPropertyValue("NumberFormat", nFormat)
End Sub

Function fnGetNumberFormatId(oDoc, sNumberFormat)
sCharLocale = oDoc.getPropertyValue("CharLocale")
nFormatId = oDoc.getNumberFormats.queryKey(sNumberFormat, sCharLocale, false)
IF nFormatId = -1 Then
nFormatId = oDoc.getNumberFormats.addNew(sNumberFormat, sCharLocale)
End If
fnGetNumberFormatId = nFormatId
End Function


Good luck.

JD
_________________
My Extensions
Color2Rows and LastSession
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jan 16, 2009 3:37 am    Post subject: Reply with quote

Quote:
but the date reads of my macro isn't for example 22/12/08, but is 39804.

... which is the correct cell-value for the date as double-precision floating point number.
You can simply use Basic function cDate to convert this to a Basic value of type Date from where you can get year, month, day, hour, minut, second, cDateToISO-strings, whatever.
BUT! cDate(double) always assumes 1899-12-30 as day # zero.
You can easily test if the document in question uses the same day (as UNO-type c.s.s.util.Date). If not, add the difference of days.

Code:

Sub Main
oSh = ThisComponent.Sheets.getByIndex(0)
oCell = oSh.getCellByPosition(0,0)
dblCellValue = oCell.getValue()
oUno = thiscomponent.NullDate
dt = DateSerial(oUno.Year,oUno.Month,oUno.Day)
intDt = cInt(dt)
dblX = dblCellValue + intDt
dtX = cDate(dblX)
strDt = cDateToISO(dtX)
print strDt
End Sub

prints "19991231" or "20090116" for today's date.
Roll your own format string or play with Basic runtime function Format (see help).

For NullDate see also Tools>Options...Calc>Calculation

EDIT: Meanwhile I tested the above code. It prints a date-string of whatever value is in the first cell of ThisComponent regardless of number format and NullDate.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group