[Solved] Converting a text string to a date
[Solved] Converting a text string to a date
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.
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
Re: Meaning of single quote (') in a cell's value
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Converting a text string to a date
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:
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
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
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
Re: Converting a text string to a date
Use Full Editor or PostReply screens and press the Code button. Using QuickReply, prefix your code by [/code] - this is probably how most volunteers do it.
Code: Select all
[code] and follow it by
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
Re: Converting a text string to a date
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Converting a text string to a date
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".
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.
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.
Code: Select all
tpSDate(1) = bData(1)(8) ' get start date and save as a Date (or a String)
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)
OpenOffice 4.1.3 on Windows 10
Re: Converting a text string to a date
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))
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Converting a text string to a date
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.
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
Re: [SOLVED] Converting a text string to a date
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice