Date: prev next · Thread: first prev next last
2018 Archives by date, by thread · List index

As usual I accidently sent my reply to the OP directly rather than to the
list, and once again I'm sorry for that. Here's what I replied:

---------- Forwarded message ----------
From: Johnny Rosenberg <>
Date: 2018-01-28 0:48 GMT+01:00
Subject: Re: [libreoffice-users] macro to only change month
To: bjlockie <>

2018-01-27 22:26 GMT+01:00 bjlockie <>:

I keep a spreadsheet with a monthly budget.
Each month I copy a month to make a new month.
I use Fill  to change the dates to the new month.
This is not ideal because sometimes there are 2 rows for the same day and
Fill changes the 2nd day.
I need a macro just to change the month and not the day.

Jan 1 2018 | debit | ...
Jan 1 2018 | credit card | ...
Jan 2 2018 | credit card | ...

Using Fill to get a new section for Feb results in:
Feb 1 2018 | debit | ...
Feb 2 2018 | credit card | ...
Feb 3 2018 | credit card | ...

What I want is:
Feb 1 2018 | debit | ...
Feb 1 2018 | credit card | ...
Feb 2 2018 | credit card | ...

I'm not sure what you want to do here. Seems like you want to first input
everything for one month, let's say January, then copy all that to empty
cells below the January cells and finally have a macro replace January with
February in those new cells, is that what you are asking for?

Dates in LibreOffice (and Apache OpenOffice, MS Excel and more) are just
numbers, usually days since 1899-12-30 (due to Excel's leap year bug).
Those numbers are then formatted to display a date format as desired.
That's nakes it slightly more tricky to manipulate dates, but fortunately
there are functions available for the job.

For instance there's a cell function called EDATE, which returns a date
value that is x months after another date. For instance if A1=2018-01-28
and B1=EDATE(A1,1), then B1 displays 2018-02-28 (if set to that format).
What happens though, if two months don't have the same number of days?
Well, for instance 2018-01-31 + 1 month also displays 2018-02-28.

So far no Basic macro needed, but also LibreOffice Basic have some date
functions available, for instance DateSerial(Year, Month, Day). Example:

REM  *****  BASIC  *****

Option Explicit

Sub Main
    Dim dDate As Date
    Print DateSerial(Year(dDate), Month(dDate)+1, Day(dDate))
End Sub

REM  *****  END BASIC  *****

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… But at least the month boundary is easy to work around. Before
using month in DateSerial, just do this:
Month=1+(Month-1) Mod 12

REM  *****  BASIC  *****

Option Explicit

Sub Main
    Dim Year As Integer, Month As Integer
    While Year<2020
        If Month>12 Then
            Month=1+(Month-1) Mod 12
        End If
        Print DateSerial(Year, Month, 15)
End Sub

REM  *****  END BASIC  *****

The built in Help has more infromation about this and much more.

Kind regards

Johnny Rosenberg

Sent from:

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


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.