macro to only change month

James1 · January 27, 2018, 9:26pm

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 | ...

Brian_Barker · January 28, 2018, 6:58am

You don't say what is actually in your date column: text or proper date values? I'm trusting you have the latter.
o Make your copy.
o In a spare column, enter =Xn+31 - where Xn is the first date value. Don't worry about the formatting of that cell.
o Fill down that new column to create a set of new (February) dates corresponding to your January dates.
o Cut (or copy) the new values and paste them back over the original date column, but using Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste and ensuring that both Formulas and Formats are *not* ticked in the Paste Special dialogue.

Using the formula =Xn+31, you clearly need to modify the 31 to the length of whichever month you are extending from. Alternatively, you could use other formulae to do this automatically, such as:
=DATE(YEAR(Xn);MONTH(Xn)+1;DAY(Xn))
or
=Xn+DAY(EOMONTH(Xn;0))

These formulae should roll over year ends appropriately, but in any case, you will need to reduce or extend each month's rows to account for different lengths of months, of course.

A macro? Is it worth it? That is left as an exercise for the reader!

I trust this helps.

Brian Barker

Johnny_Rosenberg · January 28, 2018, 7:52am

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:

Brian_Barker · January 28, 2018, 8:04am

Does =DATE(2017;13;59) not give 2018-02-28 for you?

Brian Barker

Johnny_Rosenberg · January 28, 2018, 11:40am

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 · January 28, 2018, 12:52pm

Whoops: sorry!

Brian Barker

Luuk · January 28, 2018, 2:45pm

You can write a FUNCTION to get next month

Function FirstOfNextMonth(dDate as Date) as Date
    if Month(dDate)>=12 then
        FirstOfNextMonth = DateSerial(Year(dDate)+1, 1, 1)
    else
        FirstOfNextMonth = DateSerial(Year(dDate), Month(dDate)+1, 1)
    end if
End Function

When you use = FirstOfNextMonth(Now()) is will return 01-02-2018 (if
your date-format = DD-MM-YYYY)

Johnny_Rosenberg · January 28, 2018, 3:30pm

You can write a FUNCTION to get next month

Function FirstOfNextMonth(dDate as Date) as Date
    if Month(dDate)>=12 then
        FirstOfNextMonth = DateSerial(Year(dDate)+1, 1, 1)
    else
        FirstOfNextMonth = DateSerial(Year(dDate), Month(dDate)+1, 1)
    end if
End Function

When you use = FirstOfNextMonth(Now()) is will return 01-02-2018 (if
your date-format = DD-MM-YYYY)

Just note that your function always returns the first day of the calculated
month. I'm not sure the OP wants that, but maybe I'm wrong.

Kind regards

Johnny Rosenberg

Luuk · January 28, 2018, 4:01pm

It's named 'FirstOfNextMonth()'with a reason!
Also note that this 'example' function can be adapted to return another
date (whatever the OP wants).

To return 'NextMonth()' next question should be solved:
What is next month, if current date is 31 januari?
a) 28 februari, of 29 februari, depending on the year being a leap year
or not
b) 3 march 2018, (31 days after 31 januari)
c) something else.....

The same question for 31 march, and other months which have a NextMonth
with less days than the current month.

Tom_Davies1 · January 28, 2018, 6:24pm

Hi
This sort of thing might be better as a database.

The advantage being that you wouldn't need a new page for each month = you
just keep entering data in the same place. The page(s) for a 'month' would
be a "Query". You could have extra queries to cover different or/and
changeable time-frames or/and to cover different aspects (such as only
outgoings). Each query adds almost nothing to file-size.

The disadvantage is it's a pain to set-up your first database and it can be
a steep learning-curve. Your existing spreadsheets might well be able to
be used as the central data source or you could save tabs in csv format to
be added to your data - but this doesn't mitigate the pain of creating a
database in the first place. If you are familiar with databases then it'd
be easy to set-up but then you'd probably be doing that already - although
sometimes it's quicker and easier to just quickly make something as a
spreadsheet to flesh-out the sort of design you want.

There are dedicated programs designed specifically to track finances. For
something for the sort of scale you're hinting at GnuCash might be good
(very scaleable and multi-platform (originally for Gnu&Linux)). If you
prefer a proprietary system that only works on Windoze then Sage Instant
(£50-£100) is prolly more than you need but people will keep trying to push
you into buying Sage Line 50 which is closer to a grand.

Regards from
a Tom

2018-01-28 15:45 GMT+01:00 Luuk <luuk34@gmail.com
<mailto:luuk34@gmail.com>>:

    You can write a FUNCTION to get next month

    Function FirstOfNextMonth(dDate as Date) as Date
        if Month(dDate)>=12 then
            FirstOfNextMonth = DateSerial(Year(dDate)+1, 1, 1)
        else
            FirstOfNextMonth = DateSerial(Year(dDate), Month(dDate)+1, 1)
        end if
    End Function

    When you use = FirstOfNextMonth(Now()) is will return 01-02-2018 (if
    your date-format = DD-MM-YYYY)

Just note that your function always returns the first day of the
calculated month. I'm not sure the OP wants that, but maybe I'm wrong.

It's named 'FirstOfNextMonth()'with a reason!
Also note that this 'example' function can be adapted to return another
date (whatever the OP wants).

To return 'NextMonth()' next question should be solved:
What is next month, if current date is 31 januari?
a) 28 februari, of 29 februari, depending on the year being a leap year
or not
b) 3 march 2018, (31 days after 31 januari)
c) something else.....

The same question for 31 march, and other months which have a NextMonth
with less days than the current month.

Johnny_Rosenberg · January 28, 2018, 10:01pm

> 2018-01-28 15:45 GMT+01:00 Luuk <luuk34@gmail.com
> <mailto:luuk34@gmail.com>>:
>
> You can write a FUNCTION to get next month
>
> Function FirstOfNextMonth(dDate as Date) as Date
> if Month(dDate)>=12 then
> FirstOfNextMonth = DateSerial(Year(dDate)+1, 1, 1)
> else
> FirstOfNextMonth = DateSerial(Year(dDate), Month(dDate)+1, 1)
> end if
> End Function
>
>
> When you use = FirstOfNextMonth(Now()) is will return 01-02-2018 (if
> your date-format = DD-MM-YYYY)
>
>
> Just note that your function always returns the first day of the
> calculated month. I'm not sure the OP wants that, but maybe I'm wrong.
>
>
It's named 'FirstOfNextMonth()'with a reason!
Also note that this 'example' function can be adapted to return another
date (whatever the OP wants).

To return 'NextMonth()' next question should be solved:
What is next month, if current date is 31 januari?

Yes, I asked that, kind of, but if I got an answer I missed it. My
suggestion was EDATE(SomeDate,1) which returns the same day of the
nextmonth, following your ”a” suggestion below.
I didn't say your suggestion was wrong, I was just wondering if it was what
the OP asked for. Maybe it was, maybe it wasn't. Maybe he will write back
and tell us.

Kind regards

Johnny Rosenberg

James1 · January 29, 2018, 1:01am

Thanks for the replies.
I'm going to look at gnucash but I suspect it does much more than I need.

Johnny_Rosenberg · January 29, 2018, 6:52am

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

Johnny_Rosenberg · January 29, 2018, 7:20am

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

Tom_Davies1 · January 29, 2018, 1:27pm

Hi
GnuCash definitely does a LOT more than you need !

The question is whether you find it easy enough to do what you need and see
the results in a way that is close enough to what you want. Ymmv as we are
all different.

If it looks like GnuCash is going to need tons of reading up about it then
it's prolly easier to keep using the system you know.

Apols and degards from
a Tom

James1 · January 30, 2018, 8:50pm

gnucash is too complex to set up.
I got some of the accounts made but then I gave up.
I'm not sure it can show me a month on a page like my spreadsheet can.
I only briefly looked at the reporting and even if it does what I want, it
isn't intuitive to me.