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 <[email protected]> Date: 2018-01-28 0:48 GMT+01:00 Subject: Re: [libreoffice-users] macro to only change month To: bjlockie <[email protected]> 2018-01-27 22:26 GMT+01:00 bjlockie <[email protected]>: > 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: [email protected] > 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: [email protected] 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
