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

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 <gurus.knugum@gmail.com>
Date: 2018-01-28 0:48 GMT+01:00
Subject: Re: [libreoffice-users] macro to only change month
To: bjlockie <bjlockie@lockie.ca>


2018-01-27 22:26 GMT+01:00 bjlockie <bjlockie@lockie.ca>:

> 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.
>
> Eg.
> 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
    dDate=Now
    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

Example:
REM  *****  BASIC  *****

Option Explicit

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

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

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


Kind regards

Johnny Rosenberg


> --
> Sent from: http://nabble.documentfoundation.org/Users-f1639498.html
>
> --
> 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