[Solved] Converting a text string to a date

Discuss the spreadsheet application
Post Reply
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

[Solved] Converting a text string to a date

Post by Herb40 »

I have several sheets involving dates that won't "behave" the way I want them to. In one sheet, I have dates of the form '2015-03-19 and in another I have a date in the form '35309. In fact, the '35309 form originated as a 'yyyy-mm-dd form.These sheets are both formed by Basic macros. In Basic debugging mode, the debugger says that the '35309 is a Double, but for some reason I can't convert it to a displayed date. What does the single quote stand for? What am I doing wrong. Thanks for your help.

Upon reading some of the Ten Things to Know posting, I see that the single quote means that the values are text strings, and not dates. But when I use cDate(cell containing text string) or cDate(cDbl(cell)) the conversion doesn't work in either case.
Last edited by Herb40 on Wed Mar 25, 2015 8:31 pm, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
FJCC
Moderator
Posts: 9549
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Meaning of single quote (') in a cell's value

Post by FJCC »

The single quote signifies that the cell content is text. Whatever method you are using to set the cell contents is convincing Calc that you are entering text. Could you post your macro code? The 35309 value is the date value of Sept1, 1996. Is that the date you intended to enter?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: Converting a text string to a date

Post by Herb40 »

These values are the result of a whole bunch of Basic macros. I download data containing "dates", but need to change then from the original value to the last day of the same month. The code for this is:

Code: Select all

	for r = LBound(ncAGData)+1 to UBound(ncAGData)
		s = ncAGData(r)(0)		' get a date string
		d = cDate(s)			' convert to a date serial
		d = LastDayOfMonth(d)	' convert to last day of same month
		s = d
		ncAGData(r)(0) = s
	next r
where s is a String, d is a Date, and the ncAGData is a Variant DataArray. So I think I know where the 'yyyy-mm-dd form comes from, since I am converting the dates to strings. Perhaps I should just leave them as dates and let Calc display them the way I want. As for the '35309 form, I'll have to do some checking, since those values arise after a whole bunch of other macros have executed. I'll try to find the salient code and post it.

BTW, what do I use to post Basic code in the little code windows I see in other posts? Thanks
OpenOffice 4.1.3 on Windows 10
User avatar
RoryOF
Moderator
Posts: 35066
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Converting a text string to a date

Post by RoryOF »

Use Full Editor or PostReply screens and press the Code button. Using QuickReply, prefix your code by

Code: Select all

[code] and follow it by 
[/code] - this is probably how most volunteers do it.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting a text string to a date

Post by Villeroy »

Your code snippet does not show the cause of the problem. If you set the string of a cell you do exactly that.
The last day of a month calculated in a spreadsheet is =DATE(year;month+1;0) which calculates the zeroth day of the next month.
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
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: Converting a text string to a date

Post by Herb40 »

Here's the code that is causing my problem. I have reduced it to the bare essentials. I am scanning a previously-generated sheet to produce a report from it. The report is going to another sheet. As I scan the input sheet, I get the date value from a DataArray (bData) and save it. If I declare tpSDate() as a String, the code shown later works. If I declare it as a Date, the code below fails with the message: "Basic runtime error; object variable not set".

Code: Select all

        tpSDate(1) = bData(1)(8)	' get start date and save as a Date (or a String)
Later, produce a report line in a sheet. The eData DataArray that is being used here was previously created so I do not need a getDataArray call. But even if I include a getDataArray call, the code still fails as described.

Code: Select all

	tr = tr + 1
	s = cStr(tr)
	eName = "A" & s & ":" & eLastColName & s
	eRange = eSheet.getCellRangeByName(eName)
	for j = 0 to 3
		eData(0)(j) = ...
	next j
	eData(0)(4) = ...
	eData(0)(5) = tpSDate(1)
	eData(0)(6) = ...
	eData(0)(7) = ...
	eRange.setDataArray(eData)
The error message does not appear until the eRange.setDataArray(eData) statement. But everything in eData has been set many times. To isolate the problem, I placed a setDataArray statement after each of the assignment statements show. Then the error message appears earlier, after the eData(0)(5) = tpSDate(1) statement. As I mentioned above, this statement works if tpSDate(1) is a String, but not if it is a Date.
OpenOffice 4.1.3 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting a text string to a date

Post by Villeroy »

Sorry, I don't see how this code works, where the dates come from nor which types are being used.

A spreadsheet cell has one localized or English formula string, one value and one string. The cell value is always a double or integer. Cells do not have any dates.
objCell.setValue(cInt(Now))

The formula can be set to an ISO date.
obj.Cell.setFormula("2011-09-30")
obj.Cell.FormulaLocal = "2011-09-30"

The following sets the full date+time number even though the value is a Basic date:
objCell.setValue(Now)

But this fails:
objCell.setValue(Today)
objCell.setValue(cInt(Today))
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
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: Converting a text string to a date

Post by Herb40 »

Thanks, Villeroy,

I know that Dates are stored as Doubles. I suppose that Basic had the choice of converting the Date value (which was stored as a Double) to a String or leaving it as a Double when it was assigned to the DataArray (which can handle both numbers and strings) and chose String, which produced the value '35309. It seems like it would have made more sense to leave it as a Double.

I changed the code that saved the date to store it in a Double, rather than a Date or String. That works and produces a Double with a value of 35309, rather than a String with a value of '35309. Calc will display the Double 35309 in a date format, but will not display the String '35309 in any date format.
OpenOffice 4.1.3 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED] Converting a text string to a date

Post by Villeroy »

Dumping arrays into ranges works like this:

1) oRange.setDataArray( Array( Array( rows ) ) ) where the row values consist of strings or doubles. Blanks are empty strings. Basic Null gives #VALUE!.

2) oRange.setFormulaArray( Array( Array( rows ) ) ) where the row values consist of strings only. All numerals in simple English notation (digits with decimal points), numeric text with a leading apostrophe ( '123 ) and formula expressions with English function names and semicolon as list separator: =VLOOKUP(1.23;A1:X12;2;0).

3) oRange.setArrayFormula("TRANSPOSE(A1:X12)") where the English formula string needs to be without the leading = and without the surrounding braces of an array formula.
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
Post Reply