[Solved] How to get Max of Dates in OpenOffice macro

[pranjal_ds]

Hi All,

I have written a macro in Apache Open Office 4.1.2.
I need code to calculate the max of Dates from a column of dates in 1 sheet and then copy the max value to another sheet cell.

Please let me know how I can do this?

Thank you.

Regards,

[RoryOF]

How have you attempted this in your macro, and in what way does your code fail?

[pranjal_ds]

I have a sheet named "Pranjal Shirsat" which is having dates in column K and have another sheet named "Summary" and I have to find the max date from sheet "Pranjal Shirsat" and paste it in "Summary" sheet cell L3.

I have written code to get the max in macro CreateSheets.

Code to find max is as below, but it is returning a fraction value in variable TheMax.
I am also attaching my file containing the macro herewith.
Please check and provide me resolution.

Code: Select all

			dispatcher.executeDispatch(document, ".uno:Save", "", 0, Array())
			Cell = oSheet1.getCellByPosition(1,i)
			EmpName  = Cell.String() ' Get the employee name from empoyee name column of Summary sheet
			'msgbox EmpName
			oSheet2 = ThisComponent.Sheets.getByName(EmpName)
			 theColumn = oSheet.Columns(10)
			 eRgs = theColumn.queryEmptyCells
			 u = eRgs.Count-1
			 eRg = eRgs(u)
			 iCell1 = eRg.getCellByPosition(0, 0)
			 lCellAdd1 = iCell1.AbsoluteName
		 	 arrAdd3 = split(iCell1.AbsoluteName,".")
			 DPosStr1 = arrAdd3(1)
			 DPosStr1 = mid(DPosStr1,2,Len(DPosStr1)
		     'msgbox DPosStr1
			 arrAdd3 = split(DPosStr1,"$")
			 'msgbox arrAdd3(1)
			 lRow3 = arrAdd3(1)  ' Get the New sheet last row of Date column
			 Cell = oSheet1.getCellByPosition(11,i)
			 
			 svc = createUnoService("com.sun.star.sheet.FunctionAccess")
			'oSheet = ThisComponent.Sheets.getByName("Sheet1")
			oRng = oSheet2.getCellrangeByPosition(10,1,10,lRow3) '=A1:A11 (left, top,right,bottom)
			Data = oRng.Data
			TheMax = svc.callFunction("MAX",array(Data))
			msgbox TheMax

[RoryOF]

I haven't time to examine at present, but I suspect you might need to format your Max value as a date.

[pranjal_ds]

I used CDate(TheMax), but this returns 00/00/0000 which is not valid.

Regards,

[RoryOF]

Your dates are showing up as text; use /Value /Highlighting to see the data types.

[Villeroy]

The functionality for what you try is already built-in: Using the DataPilot (aka "Pivot Table")

Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions, use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.

[Tutorial] Date-Time Conversion in StarBasic
A US American M/D/Y string can only be converted if your office locale happens to be "English(USA)"

[pranjal_ds]

Hi RoryOF,

Please let me know how to use the code you indicated in my macro?

Regards,

[Villeroy]

In order to convert the US American date strings into valid spreadsheet dates (day numbers):
1) Select the cells in question
2) Right-click>Format... tab:Numbers,
Language: English(USA)
and any number format that is not text
3) menu:Find&Replace...
[More Options]
[X] Current Selection Only
[X] Regular Expressions
Search: .+ (a dot and a plus)
Replace: &
[Replace All]
This re-enters all strings into the modified language context of the selected cells.
Format the cells any way you like. The format does not change values

[RoryOF]

The command I indicated is a setting in OpenOffice Calc to indicate the data types in the spreadsheet. Numbers will show as blue, text as black, results of formulae as green. Select /View /Value highlighting in the Calc menu and you will see that your "Dates" are all text; you need to convert these to proper dates so that at your macro will work.

See [Tutorial] Calc date formulas
for more information.

[Villeroy]

Any text to date conversion by formulas requires that you temporarily switch to the correct locale in Tools>Options>LanguageSettings>Languages. This is something that is not covered by the date formula tutorial. My above mentioned method with find&replace into the modified cell locale works just fine. Once you have correct numeric values, you can switch back any changed locale settings back to anything you prefer.

[pranjal_ds]

Hi RoyOF/Vileroy,

In the below case I need to use Value() on a single cell but svc.CallFunction() accepts array as an input argument.
Please let me know how I can use VAlue function in svc.CallFunction.

Below code is giving error: "Basic runtime error" Object varaible not set"

svc = createUnoService("com.sun.star.sheet.FunctionAccess")
'oSheet = ThisComponent.Sheets.getByName("Sheet1")
oRng = oSheet2.getCellrangeByPosition(11,1,11,lRow3) '=A1:A11 (left, top,right,bottom)
Data = oRng.Data
TheMax = svc.callFunction("VALUE",array(Data))
msgbox TheMax

[pranjal_ds]

Hi RoyOF/Vileroy,

I was able to generate the max of the dates in summary sheet by first converting the dates in cell range to long value and then finding hte max of the long value and then converting back the long value to date value.

This question is closed now.

Thank you.

Regards,