[Libreoffice-bugs] [Bug 139363] BASIC: SerialDate() only accept the most obvious month and day values.

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=139363

--- Comment #2 from Johnny Rosenberg <guraknugen at gmail.com> ---
Ooops, my bad. I'm terribly sorry. I don't know what happened there. I meant
DateSerial() all the time. I'm not sure from where I got SerialDate().

I don't understand how this happened. I even used DateSerial() in the
workaround function DateSerialA(), so I'm quite puzzled now.

There doesn't seem to be a way to change my first post (or any posts), so here
it is again, corrected:

————————————————————————————————————————————————————————————————————————————————
The BASIC command DateSerial() doesn't accept months and days out of bounds.
The Calc cell-function DATE() does and so does Excel's DateSerial(), and it
would be very practical if DateSerial() also did.

Example 1:
Print DateSerial(2021,2,29)
Gives an error message.
In a spreadsheet cell: =DATE(2021;2;29)
Result: 2021-03-01 (if cell is formatted properly, as YYYY-MM-DD, of course)
Expected result: 2021-03-01

Example 2:
Find the last date in February 2021:
Print DateSerial(2021,3,0)
Error message.
In a spreadsheet cell: =DATE(2021;3;0)
Result: 2021-02-28
Expected result: 2021-02-28

Example 3:
Just about anything, such as:
Print DateSerial(2021,-73,-4500)
Error message.
In a spreadsheet cell: =DATE(2021;-73;-4500)
Result: 2002-07-06
Expected result: 2002-07-06

Example 4:
Loop through months:

Dim dtCurrentDate As Date, dtDate As Date
Dim iYear As Integer, iMonth As Integer, i As Integer

dtCurrentDate=Now()
iYear=Year(dtCurrentDate)
iMonth=Month(dtCurrentDate)

For i=-100 To 100
        dtDate=DateSerial(iYear, iMonth+i, 1)
        ' Print dtDate somewhere or whatever.
Next i

The above only works as long as 1≤(iMonth+i)≤12, otherwise there is an error.
Expected result: It should loop through all months and figure out the dtDate
values without questioning anything.


Workaround:
A custom function for it. Here are mine, I wrote two different global ones:

' This one adjusts the input values before using the built in DateSerial.
Public Function DateSerialA(iYear As Integer, iMonth As Integer, iDay As
Integer) As Date
        If iMonth>12 Or iMonth<1 Then
                Dim iYearDiff As Integer
                iYearDiff=int((iMonth-1)/12)
                iYear=iYear+iYearDiff
                iMonth=iMonth-12*iYearDiff
        End If
        DateSerialA=DateSerial(iYear, iMonth, 1)+iDay-1
End Function

' This one simply use Calc's DATE() function.
Public Function DateSerialC(iYear As Integer, iMonth As Integer, iDay As
Integer) As Date
        Dim CalcFunction As com.sun.star.uno.XInterface
        CalcFunction=createUnoService("com.sun.star.sheet.FunctionAccess")
        DateSerialC=CalcFunction.callFunction("DATE", Array(iYear, iMonth,
iDay))
End Function

If not using extended data types, Dim CalcFunction As Object instead, of
course.

Example 3 again:
Print DateSerialA(2021,-73,-4500)
Result: 2002-07-06
Print DateSerialC(2021,-73,-4500)
Result: 2002-07-06
Both match the expected results.

I just find it very strange and inconsistent that DateSerial() is so primitive
compared to the DATE() cell function in Calc. Accepting just about any integer
for month and day is very useful in loops and makes the code more compact and
easier to understand.
————————————————————————————————————————————————————————————————————————————————

Thanks for the
https://opengrok.libreoffice.org/xref/core/basic/source/runtime/methods.cxx?r=3482f590#4757
link. I had a quick look. I'm not a programmer so I'm not exactly sure what I
was looking at, but one of the comments seemed interesting to me:

/* TODO: we could enable the same rollover mechanism for StarBASIC to be
 * compatible with VBA (just with our wider supported date range), then
 * documentation would need to be adapted. As is, the DateSerial() runtime
 * function works as dumb as documented... (except that the resulting date
 * is checked for validity now and not just day<=31 and month<=12).
 * If change wanted then simply remove overriding RollOver here and adapt
 * documentation.*/

Isn't this what I'm asking for? I'm not sure, but it looks a bit like it to me,
but maybe I just don't understand what I'm reading.
I just thought it might be a good idea to be compatible with VBA, since many
LibreOffice users switched from Microsoft Office, at least that's what it feels
like when reading LibreOffice forums and mailing lists.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210102/3afad380/attachment.htm>