[Solved]User-defined date format (yyyymmdd) & data to column

[Kprogrammer]

oCell2 = Sheet1.getCellrangeByPosition(5,0,5,n-1)

oCell2 contains date as 28-Aug-2018.

I have to apply these formatting in oCell2
1)user-defined date format (yyyymmdd)
2)Data->Text to column -> fixed Width

after formatting 28-Aug-2018 change to 20180828

Please guide.

[Villeroy]

You must NOT import dates as text. Import dates as numbers.

oCell2.CellStyle = "DateStyle" applies all the formatting attributes of your custom cell style "DateStyle" to the range.
If oCell2 contains numbers, the numbers will be formatted according to the number format of your cell style.

[Lupp]

The question is very unclear to me.

-1- Are the dates actually texts?
A cell object always has the .Value and the .String property. Did you report .String to be the "content"?
Or did you just report what you viewed in the cell grid?

-2- Is it about dates imported as texts?

-3- Is it about dates formerly expressly converted to text?

-4- If text-dates: Is there urgent need to do the conversion by custom code? Why?

-5- If standard dates being just numbers formatted for the view: You simply change the display format in the respective dialogue.

-6- Shall the results be numbers or texts?

[Kprogrammer]

Date is number '28-Aug-2018
It is stored as number .
and i have to make it user defined-yyyymmdd
then to apply text to column ->fixed
so that i can get 20180828

[Kprogrammer]

Below is the code recorded by maro.

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = true

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "NumberFormatValue"
args3(0).Value = 120

dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args3())

rem ----------------------------------------------------------------------

How can i use this in cell range.
oCell2 = Sheet1.getCellrangeByPosition(5,0,5,n-1)

i tried oCell2.NumberFormat=120
but didn't work.

[Lupp]

Date is number '28-Aug-2018
It is stored as number.

Locale? Suppose it's an English one and the format code is DD"-"MMM"-"YYYY or equivalent.

And i have to make it user defined YYYYMMDD .

Why not simply do so then: Create (as Villeroy already suggested) a named cell style having set this number format code and apply it by a doubleclick in the 'Styles and Formatting' pane to any selected range - or even to a multirange selection - probably extended over multiple selected sheets. There is no way to do so by a "macro" without lots of complications.
If you want to be able to overlay the new numbr style over already assigned named cell styles, you can use a conditional format.

...then to apply text to column ->fixed
so that i can get 20180828

I'm completely lost now. For what that? If the dates were numbers and you applied the wanted number format code, you are done.

You did NOT answer most of my questions, in specific for what reason you think you need a "macro".

If you insist on doing such things by "macros" you need to study the API ways. Recorded macros will rarely be of much use.

BTW
Despite the fact that YYYYMMDD is the original ISO 8601 format for dates, it is problematic for interfaces, those of spreadsheets and those of humans as well. That's because it doesn't tell "I'm a date". ISO assumed dates in that format to be transmitted as texts for purely technical purposes in contexts anticipating the date semantic. Where general numbers may also occur the ISO 8601 "extended" format should be used: YYYY-MM-DD .
Cells number-formatted YYYYMMDD will not accept dates entered in this format. You will have to use the extended variant.

[MrProgrammer]

oCell2 = Sheet1.getCellrangeByPosition(5,0,5,n-1)

This is macro garbage but you've posted in the Calc forum. Again. If you want a macro answer, post in the Macros and UNO API forum. I will give you an answer for the normal user interface. I will not help you further if you continue to post in the wrong forum.

… contains date as 28-Aug-2018. I have to apply these formatting 1)user-defined date format (yyyymmdd) 2)Data->Text to column -> fixed Width

This is simple unless you try to use an evil macro where you will waste hours of your time.
• Format the column with Numbers → Format code → YYYYMMDD.
• Select the column and use Data → Text to Columns to set the column type to text.
• Format the column with Numbers → Category → Text, preferably using a style.
Learn how to use Text to Columns with this tutorial. Then you can accomplish tasks like this in seconds, especially if you have predefined styles for YYYYMMDD and Text.

You did NOT answer most of my questions, in specific for what reason you think you need a "macro".

People like that are very difficult to help. It may not be worth the effort. They are asking for free assistance with their special project but don't want to contribute towards a solution.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Kprogrammer]

@MrProgrammer

I am sorry that I have posted macro queries in wrong section. I make sure from next time I would post in correct section.
Thanks for the solution.
Again , I am very sorry not answering properly to the questions asked to me by @Lupp.
I accept my mistake and I make sure that I will not repeat
it again.

[Kprogrammer]

You must NOT import dates as text. Import dates as numbers.

oCell2.CellStyle = "DateStyle" applies all the formatting attributes of your custom cell style "DateStyle" to the range.
If oCell2 contains numbers, the numbers will be formatted according to the number format of your cell style.

Date
31-Aug-2018
30-Aug-2018

this is how date is stored in the sheet. it is stored as number.
dim getcell
dim celldata
getcell = Sheet1.getCellByPosition(0, 1)
celldata = getcell.getvalue()
msgbox data
data.CellStyle = "DateStyle"

Tried to get the value of the cell using getvalue().The message box displayed 0 but it should show 31-Aug-2018.
Please help what's wrong in the code.

[robleyd]

Try using .Type to determine the actual type of data in the cell. There is an example on this wiki page

You say that data is stored as 31-Aug-2018 - if this is in fact the case then the type of data would be Text; that date would, if stored as a number, be stored as 43343 but could be formatted to display in many ways..

[RoryOF]

Don't forget that Calc can use three base dates for date display, set on /Tools /Options /OpenOffice Calc /Calculate, two of which allow a one day variation in the displayed date.

[Kprogrammer]

Try using .Type to determine the actual type of data in the cell. There is an example on this wiki page

You say that data is stored as 31-Aug-2018 - if this is in fact the case then the type of data would be Text; that date would, if stored as a number, be stored as 43343 but could be formatted to display in many ways..

The cell containing the value 31-Aug-2018 is number as on selecting the cell I can see the value as '31-Aug-2018
And I right clicked on the cell selected the option format cell then it showed number. Thus I said it is stored as number.

When I am trying to get the value using getValue() it is not showing 31-Aug-2018 but 0.Not able to understand why it's not showing the actual value.

I stored 31-Aug-2018 in a date variable and changed the format to yyyymmdd it's working fine.

Dim mydate as Date
Dim convdate
mydate ="31-Aug-2018"
Convdate = format(mydate,'yyyymmdd')
Msg box convdate

[robleyd]

The cell containing the value 31-Aug-2018 is number as on selecting the cell I can see the value as '31-Aug-2018
And I right clicked on the cell selected the option format cell then it showed number. Thus I said it is stored as number.

The apostrophe prefix tells Calc that the data is to be stored as text; even if the cell is formatted as number, date or whatever the content is still text. Hence you get zero when you use getValue. If you use .Type you should be able to confirm this.

However, you seem to have your own idea of how this all works, so I don't think I need to contribute further.

[Kprogrammer]

The cell containing the value 31-Aug-2018 is number as on selecting the cell I can see the value as '31-Aug-2018
And I right clicked on the cell selected the option format cell then it showed number. Thus I said it is stored as number.

The apostrophe prefix tells Calc that the data is to be stored as text; even if the cell is formatted as number, date or whatever the content is still text. Hence you get zero when you use getValue. If you use .Type you should be able to confirm this.

I used.Type as told by you and confirmed that it's text.

dim getcell
dim celldata
getcell = Sheet1.getCellByPosition(0, 1)
celldata = getcell.getvalue()
Select Case getcell .Type
Case com.sun.star.table.CellContentType.EMPTY
MsgBox "Content: Empty"
Case com.sun.star.table.CellContentType.VALUE
MsgBox "Content: Value"
Case com.sun.star.table.CellContentType.TEXT
MsgBox "Content: Text"
Case com.sun.star.table.CellContentType.FORMULA
MsgBox "Content: Formula"
End Select

It displayed Content: Text.

Please answer How can I get the actual value as it is showing 0 and the type is text??

However, you seem to have your own idea of how this all works, so I don't think I need to contribute further.

No, i was trying ,just got confused so i wrote how i confirmed that and as you replied my confusion got cleared.By this , i am able to understand the solution and where i went wrong .Please don't take me wrong.
Thank you for your reply.
I need your contribution so that i can put my efforts in right direction.

[Kprogrammer]

Don't forget that Calc can use three base dates for date display, set on /Tools /Options /OpenOffice Calc /Calculate, two of which allow a one day variation in the displayed date.

Thank you for the information.

[Kprogrammer]

As the type is text it is displaying 0.
I tried converting the type to date using CDate() so that I can format it but Didn't work.
Please guide me.
Thanks in advance.

[Lupp]

In this thread we had 14 posts now with little progress. This despite the fact that the needed information is given in the guides and additional documents, and in many related threads in this forum, and in other forums too.
In specific there is a tutorial named "Ten concepts that every Calc user should know" (viewtopic.php?f=75&t=39529). Chapters 3 hrough five should apply.
Did anyone having posted in this thread except Villeroy, MrProgrammer, robleyd, RoryOF, and myself ever read it?

[Villeroy]

Any Excel book of the 90ies explains all this in depth.

[Kprogrammer]

In this thread we had 14 posts now with little progress. This despite the fact that the needed information is given in the guides and additional documents, and in many related threads in this forum, and in other forums too.
In specific there is a tutorial named "Ten concepts that every Calc user should know" (viewtopic.php?f=75&t=39529). Chapters 3 hrough five should apply.
Did anyone having posted in this thread except Villeroy, MrProgrammer, robleyd, RoryOF, and myself ever read it?

Thank you for the help.
Yes I went through the tutorial.
Also I read the two posts related to this.

(viewtopic.php?f=9&t=60198)
viewtopic.php?f=20&t=3069

I will read the tutorial again , clear my concepts well and will try again.

[RoryOF]

It is often helpful for familiarisation to use a small trial file, rather than to attempt to sort out a problem on an importat and larger file.

[Kprogrammer]

Any Excel book of the 90ies explains all this in depth.

Thanks for your reply.
I realize that I am not applying the provided solution correctly. I will clear my concepts well then will be able to do it.

[Kprogrammer]

It is often helpful for familiarisation to use a small trial file, rather than to attempt to sort out a problem on an importat and larger file.

Thank you.
I will follow your advice.I will try again with a small trial file.

[robleyd]

I think we may have a case of the XY Problem here.

We are not told how the dates get into the spreadsheet. The basic problem appears to be that dates are being imported as text. The solution proposed by OP is a macro to convert them to the desired format.

If this is the case, use the Detect Special Numbers option when importing data. If this isn't the case, tell us exactly how the values find their way into your spreadsheet. Don't make us guess and waste both our and your time.

[Zizi64]

oCell2 = Sheet1.getCellrangeByPosition(5,0,5,n-1)

oCell2 contains date as 28-Aug-2018.

I have to apply these formatting in oCell2
1)user-defined date format (yyyymmdd)
2)Data->Text to column -> fixed Width

after formatting 28-Aug-2018 change to 20180828

Please guide.

Date
31-Aug-2018
30-Aug-2018

this is how date is stored in the sheet. it is stored as number.

- Please upload a real, ODF type sample file here - instead of the textual samples. We can not check the existing type of the data based on the typed-in strings in this forum.
- Please upload your macro code (embedded in the sample document).
- Use Cell Styles for formatting of the cells - instead of the direct formatting method.

[Kprogrammer]

Hi,
Here is the details of my task for which i am writing macro code:-
There are three files.
1) Source File :- ProductUpdate31-Aug-2018(csv File)
2)Destination File:-PRODUCT_TEMP(xls File)
3)FINAL PREPARED FILE:-PRODUCT_AUG_31(xls file)

Source file data and no of records changes daily.
There are some steps to be followed to prepare destination file(PRODUCT_TEMP(xls File)) using source file(ProductUpdate31-Aug-2018(csv File)) then it has to be saved as a new file (PRODUCT_AUG_31(xls file)).The steps are:-

1)copy column A(Product Code) from source file and paste into column A(pro_identifier)
of destination file as paste special text ,numbers operations->none, shift cell->don't shift

2)copy column C(Unit Price) from source file and paste into column B(price)
of destination file as paste special text ,numbers operations->none, shift cell->don't shift

3)copy column B(Date) from source file and paste into column C(price_date)
of destination file as paste special text ,numbers operations->none, shift cell->don't shift
then apply
Format cell- user defined->yyyymmdd
Data->Text to column

so that 31-Aug-2018 is converted into 20180831.
This format (20180831) is expected.
as in this format data has to be loaded in the system which is fixed.

4)Add or remove values of column D and E as required.
This is because in source file no. of records varies.
In this sheet(source file) no of records are 450 if it is 500 then have to add values
of column D and E.

5)select the entire record ctrl+shift+end format cell->text and save as .xls

These are the 5 steps that i have to follow daily.
there are in total 8 files which has to prapared daily having different steps but these 5 are common to all.
It takes long time.It is tedious too.
so i decided to write macro so that on a single click it will be done.

By reading the open office documents, basic guides, and with the help of this forum
i have written code for step 1,2,4,5 which is working fine.
but i am not able to code for step no 3, which i am trying hard but not getting.

This is my another problem similar to step no 3.
one more file has date stored in ths format.since there are multiple dates.
consider these dates 30-Aug-2018, 31-Aug-2018, 02-Sep-2018.

so the latest date(02-Sep-2018) has to be stored in the column of destination file
so i wrote code for sorting descending order but facing problem.
I thought if i sort it in descending order then i will get the latest date(02-Sep-2018) in the first cell of that column but 31-Aug-2018 is coming up.
so what my idea is:-
first change the date format(30-Aug-2018) to dd-mm-yyyy(30/08/2018), when i sort this format i am getting the desired result(getting latest date(02/09/2018) in the first cell)
I tried this manually and will format again to (02-Sep-2018) as only in this format it has to be pasted in destination file.

so i have to work on step no 3 to complete my macro code which i am trying to do with the help of the forum posts.
I know you all are providing me solutions but when i am trying not able to get the desired result.
As i am going through many documents at the same time that is why i tried in multiple directions.
so i asked for the guidance.

Please find the attached files.

I hope i have provided all the details.

Thanks & Regards
Kprogrammer

[Kprogrammer]

Attaching the macro code.
PFA

[Kprogrammer]

oCell2 = Sheet1.getCellrangeByPosition(5,0,5,n-1)

oCell2 contains date as 28-Aug-2018.

I have to apply these formatting in oCell2
1)user-defined date format (yyyymmdd)
2)Data->Text to column -> fixed Width

after formatting 28-Aug-2018 change to 20180828

Please guide.

Date
31-Aug-2018
30-Aug-2018

this is how date is stored in the sheet. it is stored as number.

- Please upload a real, ODF type sample file here - instead of the textual samples. We can not check the existing type of the data based on the typed-in strings in this forum.
- Please upload your macro code (embedded in the sample document).
- Use Cell Styles for formatting of the cells - instead of the direct formatting method.

Thank you for the guidance.
- have uploaded files that i am using(3 files:- one csv and two xls)
The files are named as:-
PRODUCT_AUG_31.xls
PRODUCT_TEMP.xls
ProductUpdate31-Aug-2018.csv

-have uploaded my macro code
sorry couldn't get how to embed in Sample document so uploaded a file containing the code.
It is named as PRODUCT_FILE_MACRO_CODE_KPROGRAMMER.doc
-should i use Cell Styles like this
data.CellStyle = "DateStyle"
instead of
mydata = "30-Sep-2018"
data= Format(mydata,"yyyy-mm-dd")

these things came in my mind. Don't know if i am right. Please correct me.

[Kprogrammer]

I think we may have a case of the XY Problem here.

We are not told how the dates get into the spreadsheet. The basic problem appears to be that dates are being imported as text. The solution proposed by OP is a macro to convert them to the desired format.

If this is the case, use the Detect Special Numbers option when importing data. If this isn't the case, tell us exactly how the values find their way into your spreadsheet. Don't make us guess and waste both our and your time.

I have given the details for what purpose i am writing macro code and the progress made by me.
I hope this will not lead to XY problem which currently i am facing.

The format of the spreadsheet is fixed.Have attached the sheets with code.
The attachements are named as:-
PRODUCT_AUG_31.xls
PRODUCT_TEMP.xls
ProductUpdate31-Aug-2018.csv
PRODUCT_FILE_MACRO_CODE_KPROGRAMMER.doc

Thank You

[Zizi64]

- have uploaded files that i am using(3 files:- one csv and two xls)

Always use the native, international standard XML based ODF fileformat for your important spreadsheet documents instead of the old, obsolete, Microsoft related binary .xls type.


Your signature is:

Open office 3.1 on Windows 7

Are you really using that older version of the OpenOffice??? Please update your signature - if you are using a newer version.

[Kprogrammer]

- have uploaded files that i am using(3 files:- one csv and two xls)

Always use the native, international standard XML based ODF fileformat for your important spreadsheet documents instead of the old, obsolete, Microsoft related binary .xls type.

Thank you for the suggestion.
Will use ODF fileformat .
Your signature is:

Open office 3.1 on Windows 7

Are you really using that older version of the OpenOffice??? Please update your signature - if you are using a newer version.

I am using Open office 3.1 version only.