Re: Fwd: [libreoffice-users] macro to only change month

I just realised that MyDateSerial doesn't work when lMonth is negative,
since, for instance, Int(-2.5) returns -3 and not -2, so here's a new
version of the workaround that hopefully fixes that:

Option Explicit

Public Function MyDateSerial(lYear As Long, lMonth As Long, lDay As Long)
As Date
    lYear = lYear + Sgn(lMonth) * Int(Abs(lMonth - 1) / 12)
    lMonth = 1 + (lMonth - 1) Mod 12
    MyDateSerial = DateSerial(lYear, lMonth, 1) + lDay - 1
End Function

I just added Abs and Sgn, so this code doesn't look much messier than the
old one did, and as far as I can tell, it works. I haven't tested it for
negative years though, but I see no reason why that wouldn't work as well,
unless LibreOffice's DateSerial doersn't accept them.

This test function verifies that MyDateSerial returns the same values as
the Excel version of DateSerial (this subroutine works in Excel only):
Sub Test()
    Dim x As Date, y As Long, m As Long, d As Long
    y = 2019
    m = -14
    d = -59
    x = MyDateSerial(y, m, d)
    Debug.Print x
    Debug.Print DateSerial(y, m, d)
End Sub



Kind regards

Johnny Rosenberg

2018-01-29 7:52 GMT+01:00 Johnny Rosenberg <gurus.knugum@gmail.com>:

> Just for fun, here's a workaround for the DateSerial issue in LibreOffice
> Basic (the fact that things like DateSerial(2018,13,59) isn't allowed). I
> am at work so I could only test this in Excel (in which the function isn't
> needed anyway), but it should work in LibreOffice Calc as well:
>
> REM ***** BASIC *****
> Option Explicit
>
> Public Function MyDateSerial(lYear As Long, lMonth As Long, lDay As Long)
> As Date
>     lYear = lYear + Int((lMonth - 1) / 12)
>     lMonth = 1 + (lMonth - 1) Mod 12
>     MyDateSerial = DateSerial(lYear, lMonth, 1) + lDay - 1
> End Function
> REM ***** END BASIC *****
>
> Then use MyDateSerial instead of DateSerial, and you don't need to verify
> month and date values, it will work as in Excel, but maybe a bit slower.
> Hopefully not too slow, though.
>
> Test (in Excel):
>
> Debug.Print MyDateSerial(2018, 13, 59)
> 2019-02-28
>
> Debug.Print MyDateSerial(2018, 14, 59)
> 2019-03-31
>
> Debug.Print MyDateSerial(2019, 14, 59)
> 2020-03-30
>
> The result of last example is of course expected, since 2020 is a leap
> year.
>
> Thanks to the ",1) + lDay - 1" at the last line in the function, we get
> away with it without having to check how many days each month has, which
> makes the function look short and not so messy.
>
>
> A little bit off topic, but since I mentioned it as a possible "bug" I
> thought it would be appropriate to suggest a workaround for it. :)
>
>
> Kind regards
>
> Johnny Rosenberg
>
>
> 2018-01-28 12:40 GMT+01:00 Johnny Rosenberg <gurus.knugum@gmail.com>:
>
> > 2018-01-28 9:04 GMT+01:00 Brian Barker <b.m.barker@btinternet.com>:
> >
> > > At 08:52 28/01/2018 +0100, Johnny Rosenberg wrote:
> > >
> > > > For some strange reason you need to make sure you enter valid values
> > > > for month and day. In Excel (which sucks in most aspects, but I have to use
> > > > it at work) you can do things like DateSerial(2017, 13, 59), which returns
> > > > the date value for 2018-02-28, but that seems to throw an error in
> > > > LibreOffice. That's odd, or at least primitive, I think. It would be very
> > > > convenient not having to think about those boundaries, just increase the
> > > > month number and it just works. Maybe I should file a bug report or
> > > > enhancement request about that…
> > >
> > > Does =DATE(2017;13;59) not give 2018-02-28 for you?
> >
> > Yes, it does. I was referring to the Basic function DateSerial:
> >
> > Sub Main
> >     Print DateSerial(2017,13,59) ' Gives error message.
> > End Sub
> >
> >
> > > Brian Barker
> > >
> > > --
> > > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > > Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-un
> > > subscribe/
> > > Posting guidelines + more: https://wiki.documentfoundatio
> > > n.org/Netiquette
> > > List archive: https://listarchives.libreoffice.org/global/users/
> > > All messages sent to this list will be publicly archived and cannot be
> > > deleted

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted