LibreOffice - Date formula possible

Is there a formula that would put the date of last Friday of every month starting with April into 12 columns, it's when 1 of my pensions is paid, the other is 4 weekly which is easier to formulate.

OR

failing that, the last day of the next month from "=DATE(2015,4,30)"

I tried in D17 "=DATE(2015,4,30)+(DAYSINMONTH(5))" that was okay but when I tried the next column using =D17+(DAYSINMONTH(6)) it was incorrect showing 01 JLY 2015 in other words okay if 30 days but incorrect if not 30 days for all columns

You could start from the DAYSINMONTH command and then look at the day of the last date and then work back to the previous Friday if it isn't a Friday

That was what I had to do to get the correct end of month date (don't know why that was going wrong) e.g -3 for Feb and -1 if 31 days

Sorry about the delay I had to install the programme.

Assuming that the fiirst of jan is in A24, then in b24

=DATE(YEAR(A24),MONTH(A24),DAYSINMONTH(A24))-IF(WEEKDAY(DATE(YEAR(A24),MONTH(A24),DAYSINMONTH(A24)),2)>4,
WEEKDAY(DATE(YEAR(A24),MONTH(A24),DAYSINMONTH(A24)),2)-5,WEEKDAY(DATE(YEAR(A24),MONTH(A24),DAYSINMONTH(A24)),2)+2)

Will give the last friday in jan.

Can see this here on a sample spreadsheet, done in two sections, firstly producing it with several columns and then in a single formula.

Note if you copy the formula above then copy it to a text editor first to remove the line break.

Wow! that works a treat, what I did was make a small look-up table:

=DATE(S16,T16,1) where S16=year(2014) and T16=month(04) altering T16 in the formula to (T16+1,2,3,,etc)to get every month in a table, then put your formula in the 2nd column of the table to get the last Friday. All I have to do is change the year (S16) when we get to a new one

Thanks a bunch

Glad to hear it.

I think I can see what you are doing with you formulae, but I always hate entering them more than once.

So to get the date of the first month I would have used your formula
DATE(S16,T16,1) and lets say this goes into cell B2.

To get the first of the next in say cell B3 I would enter the formula,

DATE(S16,MONTH(B2)+1,1)

This I would then fill down to B13 to get the 12 months.

So that it looks nice I would probably format all those cells B2:B13 with the custom format of

MMM so that they read Apr, May .. Dec or
MMMM so that they read April, May, .... December.

Almost, had to change the year as well so it read =DATE(YEAR(S17),MONTH(S17)+1,1) otherwise the referenced year caused an Error because it always references the one above.

I already had it formatted for DD MMM YYYY

Thanks once again, it looks a lot better now and easy to create a new sheet for each financial year, it was okay when I was working as they paid me 4 weekly and all I had to do was reference the last date in the previous sheet and add 28 (which I still do for the pension from them), it was the second pension from BT that was causing the problem as that comes in on the last Friday of every month.

Now just the old age pension to figure out, I have no idea when that is paid (yet), except that every time I get close they move it up a bit.

Luckily, I have just under 3 years to go before I get that, I retired early because I had the equivalent of 40 years in the pension fund at work, so there was no reason to stay on, and apart from the colleagues, I was getting fed up with it, am now enjoying doing very little.