Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User The_Voyager
(committed) Fri 24-Jan-14 09:57:01
Print Post

LibreOffice - Date formula possible


[link to this post]
 
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

Bob WRBRIX
PN Unl.Fibre - Fritz! 7390 ~ Sync 79.99/20 Mb/s Avg 74.54/18.62 Mb/s @ 320m
DialUp to CIX, BT Home Highway to CIX, ADSL to Nildram, SKY & Be*Unlimited, Fibre to BT http://www.thinkbroadband.com/ping/share/049baa48c1f...
Standard User Oldjim
(fountain of knowledge) Fri 24-Jan-14 10:56:04
Print Post

Re: LibreOffice - Date formula possible


[re: The_Voyager] [link to this post]
 
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
Standard User The_Voyager
(committed) Fri 24-Jan-14 11:28:10
Print Post

Re: LibreOffice - Date formula possible


[re: Oldjim] [link to this post]
 
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

Bob WRBRIX
PN Unl.Fibre - Fritz! 7390 ~ Sync 79.99/20 Mb/s Avg 74.54/18.62 Mb/s @ 320m
DialUp to CIX, BT Home Highway to CIX, ADSL to Nildram, SKY & Be*Unlimited, Fibre to BT http://www.thinkbroadband.com/ping/share/049baa48c1f...


Register (or login) on our website and you will not see this ad.

Standard User philippercival
(fountain of knowledge) Fri 24-Jan-14 13:04:39
Print Post

Re: LibreOffice - Date formula possible


[re: The_Voyager] [link to this post]
 
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.

http://speedtest.net/result/2459383290.png

Solar Results
Nov  143,  Dec  125,  Jan  77,  Feb 174,  Mar  210,  April 384, May 450, 
June 485, July 512, Aug 433, 
Max Day  25.2
Standard User The_Voyager
(committed) Fri 24-Jan-14 16:27:08
Print Post

Re: LibreOffice - Date formula possible


[re: philippercival] [link to this post]
 
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 smile

Bob WRBRIX
PN Unl.Fibre - Fritz! 7390 ~ Sync 79.99/20 Mb/s Avg 74.54/18.62 Mb/s @ 320m
DialUp to CIX, BT Home Highway to CIX, ADSL to Nildram, SKY & Be*Unlimited, Fibre to BT http://www.thinkbroadband.com/ping/share/049baa48c1f...
Standard User philippercival
(fountain of knowledge) Fri 24-Jan-14 16:56:53
Print Post

Re: LibreOffice - Date formula possible


[re: The_Voyager] [link to this post]
 
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.

http://speedtest.net/result/2459383290.png

Solar Results
Nov  143,  Dec  125,  Jan  77,  Feb 174,  Mar  210,  April 384, May 450, 
June 485, July 512, Aug 433, 
Max Day  25.2
Standard User The_Voyager
(committed) Fri 24-Jan-14 17:39:39
Print Post

Re: LibreOffice - Date formula possible


[re: philippercival] [link to this post]
 
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 smile

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.

Bob WRBRIX
PN Unl.Fibre - Fritz! 7390 ~ Sync 79.99/20 Mb/s Avg 74.54/18.62 Mb/s @ 320m
DialUp to CIX, BT Home Highway to CIX, ADSL to Nildram, SKY & Be*Unlimited, Fibre to BT http://www.thinkbroadband.com/ping/share/049baa48c1f...
Standard User philippercival
(fountain of knowledge) Fri 24-Jan-14 19:17:17
Print Post

Re: LibreOffice - Date formula possible


[re: The_Voyager] [link to this post]
 
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.

http://speedtest.net/result/2459383290.png

Solar Results
Nov  143,  Dec  125,  Jan  77,  Feb 174,  Mar  210,  April 384, May 450, 
June 485, July 512, Aug 433, 
Max Day  25.2
Standard User The_Voyager
(committed) Fri 24-Jan-14 19:53:54
Print Post

Re: LibreOffice - Date formula possible


[re: philippercival] [link to this post]
 
smile 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.

Bob WRBRIX
PN Unl.Fibre - Fritz! 7390 ~ Sync 79.99/20 Mb/s Avg 74.54/18.62 Mb/s @ 320m
DialUp to CIX, BT Home Highway to CIX, ADSL to Nildram, SKY & Be*Unlimited, Fibre to BT http://www.thinkbroadband.com/ping/share/049baa48c1f...
  Print Thread

Jump to