[Libreoffice-bugs] [Bug 139363] New: 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

            Bug ID: 139363
           Summary: BASIC: SerialDate() only accept the most obvious month
                    and day values.
           Product: LibreOffice
           Version: unspecified
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: BASIC
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: guraknugen at gmail.com

The BASIC command SerialDate() 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 SerialDate() also did.

Example 1:
Print SerialDate(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 SerialDate(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 SerialDate(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=SerialDate(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 SerialDate.
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 SerialDateA(2021,-73,-4500)
Result: 2002-07-06
Print SerialDateC(2021,-73,-4500)
Result: 2002-07-06
Both match the expected results.

I just find it very strange and inconsistent that SerialDate() 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.

-- 
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/20210101/23ab9c27/attachment-0001.htm>