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.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
--
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
Context
Re: [libreoffice-users] macro to only change month · Luuk
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.