[Solved] Method for date format
[Solved] Method for date format
Hi,
I wrote a macro to convert a text 13/04/2015 (from Internet) to a date number of 42853, using Split() and join(). Now I want to convert 42853 to 15/04/13.
The Macro recording gives:
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "NumberFormatValue"
args1(0).Value = 164
Is there a method that looks something like: oCell.FormatDate.NumberFormatValue=164, that I could just add to my macro?
With so many ways to state a Date (as Text, as Date, European, US, and hundreds more) isn't there a macro on the market to clean up this mess?
I wrote a macro to convert a text 13/04/2015 (from Internet) to a date number of 42853, using Split() and join(). Now I want to convert 42853 to 15/04/13.
The Macro recording gives:
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "NumberFormatValue"
args1(0).Value = 164
Is there a method that looks something like: oCell.FormatDate.NumberFormatValue=164, that I could just add to my macro?
With so many ways to state a Date (as Text, as Date, European, US, and hundreds more) isn't there a macro on the market to clean up this mess?
Last edited by Hagar Delest on Mon Aug 07, 2017 10:17 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 4.1.5 on Mac Sierra 10.13.1
Re: Method for date format
Off topic of your actual query, but related to the macro you wrote, you state that you convert 13/04/2015 to 42853, yet when I enter Apr 13 2015 into Calc, with the Date reference in Tools -> Settings -> OpenOffice Calc -> Calculate set as default 12/30/1899, I get a return of 42107
The other two options for that setting would return even lower numbers.
I don't know where you got the information that would give you the response you state for the date you specified, but the information from Open Office Calc suggests that you may have an error in your macro.
The other two options for that setting would return even lower numbers.
I don't know where you got the information that would give you the response you state for the date you specified, but the information from Open Office Calc suggests that you may have an error in your macro.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Method for date format
It is better to apply an existing (predefined) Cell style by your macro to the target cells.Is there a method that looks something like: oCell.FormatDate.NumberFormatValue=164, that I could just add to my macro?
The Styles are the most valuable properties of the AOO and LO. Use them.
Similar topic:
http://ooo-forums.apache.org/en/forum/v ... 20&t=67938
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Method for date format
Off Topic: You are right, I gave the wrong Date Value.
Applying a style outside the macro works. Thank you.
My problem, however, is that I do not know beforehand to which Cells the Dates go.
So I prefer to apply the Cell Style from inside the macro. Any suggestions?
Thank you Det
Applying a style outside the macro works. Thank you.
My problem, however, is that I do not know beforehand to which Cells the Dates go.
So I prefer to apply the Cell Style from inside the macro. Any suggestions?
Thank you Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
Re: Method for date format
So I prefer to apply the Cell Style from inside the macro. Any suggestions?
Code: Select all
oCell.CellStyle = "MyDateStyle1"
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Method for date format
How do I create MyDateStyle1? Do I start with
MyDateStyle1= What?
MyDateStyle1= What?
OpenOffice 4.1.5 on Mac Sierra 10.13.1
Re: Method for date format
It is a cellstyle. The name string is "MyDateStyle1". You can create it manually, or by a macro code (Creating it by a macro will be more difficult for you.)How do I create MyDateStyle1? Do I start with
MyDateStyle1= What?
Creating a cell style manually:
First:
F11: Styles and formatting
or Sidebar - styles and formatting
or from the menu: Format - Styles and formatting
then:
Cell styles icon
View Listbox at the bottom of the Sidebar: Hierarchical.
then:
Right click on the Default style - New
Name the new style and adjust the properties of the new style - OK.
then :
You can use the new style in the actual document.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Method for date format
'How to create and modify a Cell style by macro' examples:
viewtopic.php?t=48960
https://wiki.openoffice.org/wiki/Docume ... /Templates
https://www.google.hu/url?sa=t&rct=j&q= ... UJxGJnPUGQ
viewtopic.php?t=48960
https://wiki.openoffice.org/wiki/Docume ... /Templates
https://www.google.hu/url?sa=t&rct=j&q= ... UJxGJnPUGQ
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Method for date format
I had created the cell style "IntDate" as you suggested earlier manually, outside the macro.
How do I refer to this Style inside the macro?
oCell.CellStyle = "IntDate" does not work
How do I refer to this Style inside the macro?
oCell.CellStyle = "IntDate" does not work
OpenOffice 4.1.5 on Mac Sierra 10.13.1
Re: Method for date format
Please upload your real ODF type sample file with your embedded macro code.How do I refer to this Style inside the macro?
oCell.CellStyle = "IntDate" does not work
Here is my example:
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Method for date format
Thanks so much. It works!! No idea what I did wrong. Det
My Code (probably very improvable) is attached for others to use.
My Code (probably very improvable) is attached for others to use.
Code: Select all
'XXXX Conversion of Text "DD/MM/YYY"Y to Date YY/MM/DD XXXXXX
Sub DateConversion
Dim oDoc As Object, oSheet As Object, oCell As Object
Dim R As Integer, C As Integer
Dim dispatcher as object
Dim NewDate As Date
'XXXXXX Get Cell Position XXXXXXX
oSheet=Thiscomponent.getcurrentcontroller.activesheet
oCell = ThisComponent.CurrentSelection
'Print oCell.CellAddress.Column, oCell.CellAddress.Row, oCell.CellAddress.Sheet
R= oCell.CellAddress.Row
C=oCell.CellAddress.Column
'Print "String or Text =", oCell.String,"Row=",R,"Col=",C
Do
oSheet.getCellByposition(C,R)
oCell = oSheet.getCellByposition(C,R)
oCell.CellStyle = "IntDate" 'Predefined in Calc
OldDate=oCell.string
If OldDate="" Then Exit Do
'Print "Old Date=", OldDate
AA=Split(OldDate,"/")
NewDate=Join(Array(AA(2),"/",AA(1),"/",AA(0)))
'Print "NewDate = ", NewDate
oCell.setValue(NewDate)
'Print " Newdatevalue",oCell.Value
'Print oCell.CellAddress.Column, oCell.CellAddress.Row, oCell.CellAddress.Sheet
R=R+1
Loop
Print "The chosen Cell in empty"
End Sub
Last edited by RoryOF on Mon Aug 07, 2017 7:56 pm, edited 1 time in total.
Reason: Added [Code] tags [RoryOF, Moderator]
Reason: Added [Code] tags [RoryOF, Moderator]
OpenOffice 4.1.5 on Mac Sierra 10.13.1