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@lists.freedesktop.org
Reporter: guraknu...@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._______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
