![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Instead of my breaking my neck trying to figure it out, I thought I'd ask.
Has anyone come up with a macro/formula/method for deriving the date (YYYY-mm-dd) of the Nth weekday of a month?
Example:
The second Tuesday of each month of 2017 would be:
2017-01-10
2017-02-14
2017-03-14
etc.
Thanks for your time in advance.
This would probably be trivial using macro, but I think writing macro in
LibreOffice isn't as fun as it could be.
If you can spare some spreadsheet cells, the following method should work.
I didn't check every details but the general idea sound correct.
The "second X of each month" can only fall between day 8 and day 14.
Similarly, each weekday happens only once in this interval (as long as a
week is 7 days long…). One can use the WEEKDAY() function to determine
which day is a given date, and using seven rows and twelve columns, one can
get a matrix of weekdays for the "second weekdays" of each month.
Once this is done, using VLOOKUP() and an extra column for the actual
result, it is possible to look for occurence of a specific weekday and
return the day of the month associated with it.
This is roughly implemented in the following file:
http://www.cjoint.com/c/GICneLdQnUI
First sheet display all the intermediate values, second sheet shows only
the result.
I often end up using a few cells together and just hide them, it allows
some pretty fun stuff to happen.
Hi Michael,
I started from this :
and adjusted it to correspond to the date sequence composition expected
by LibreOffice.
At first,this formula didn't appear to work correctly by just copy/paste
over the whole matrix of cells, but I realized that if I copied the
formula from my first cell across, then in separate copy/paste steps did
each column downwards from the first row, I could get the results I
wanted (at least for the 2nd Tuesday in 2017)
Alex
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any other MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
---- File information -----------
File: nthweekday.ods
Size: 14416 bytes.
Type: Libre-sheet
Attempt with attachment didn't seem to go thru, so sending just the message with this one.
See if this is what you are looking for.
=$C2+E$1-WEEKDAY($C2)+(E$1WEEKDAY($C2))*7+7*($B$1-1)
Is the formula I put in E2, and copied from E2 to K13
B1 contains the number of the occurance.
A2-A13 has year
B2-B13 has the month of year
C2-C13 has the date of the first day of month showing the day of week.
D2-D13 has the day of week for that date.
E1-K1 has the days of Week 1 - 7 (Sun to Mon)
E2-K13 has the forumula above.
E16-K17 has the weekday formula to confirm the date is matching the day of
week for the column.
Tried it with Nth of 1 to 5, and it seems to work in my test. If a month doesn't
have 5 of that day of week, it does give the day of ween for the next month.
Pasted the cells, but not sure it will get thru the email process. Know the list
doesn't take attachments to list, but will attache the spreadsheet for your
email.
nth
1
1
2
3
4
5
6
7
2017
1
Sunday, January 1, 2017
1
01/01/17
01/02/17
01/03/17
01/04/17
01/05/17
01/06/17
01/07/17
2017
2
Wednesday, February 1, 2017
4
02/05/17
02/06/17
02/07/17
02/01/17
02/02/17
02/03/17
02/04/17
2017
3
Wednesday, March 1, 2017
4
03/05/17
03/06/17
03/07/17
03/01/17
03/02/17
03/03/17
03/04/17
2017
4
Saturday, April 1, 2017
7
04/02/17
04/03/17
04/04/17
04/05/17
04/06/17
04/07/17
04/01/17
2017
5
Monday, May 1, 2017
2
05/07/17
05/01/17
05/02/17
05/03/17
05/04/17
05/05/17
05/06/17
2017
6
Thursday, June 1, 2017
5
06/04/17
06/05/17
06/06/17
06/07/17
06/01/17
06/02/17
06/03/17
2017
7
Saturday, July 1, 2017
7
07/02/17
07/03/17
07/04/17
07/05/17
07/06/17
07/07/17
07/01/17
2017
8
Tuesday, August 1, 2017
3
08/06/17
08/07/17
08/01/17
08/02/17
08/03/17
08/04/17
08/05/17
2017
9
Friday, September 1, 2017
6
09/03/17
09/04/17
09/05/17
09/06/17
09/07/17
09/01/17
09/02/17
2017
10
Sunday, October 1, 2017
1
10/01/17
10/02/17
10/03/17
10/04/17
10/05/17
10/06/17
10/07/17
2017
11
Wednesday, November 1, 2017
4
11/05/17
11/06/17
11/07/17
11/01/17
11/02/17
11/03/17
11/04/17
2017
12
Friday, December 1, 2017
6
12/03/17
12/04/17
12/05/17
12/06/17
12/07/17
12/01/17
12/02/17
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any other MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
---- File information -----------
File: hts_1.PNG
Size: 103678 bytes.
Type: Unknown
Well, hope you got the one sent to your direct email. This list doesn't seem to
allow either attached spreadsheets or images. Hopefully, others can figure
out the formula, or if requested can send anyone a copy of the spreadsheet
to an email that will accept it.
The formula seems to handle the problem as I understand it.
=$C2+E$1-WEEKDAY($C2)+(E$1<WEEKDAY($C2))*7+7*($B$1-1)
Hi Michael,
Well, hope you got the one sent to your direct email. This list doesn't seem to
allow either attached spreadsheets or images. Hopefully, others can figure
out the formula, or if requested can send anyone a copy of the spreadsheet
to an email that will accept it.
Yes, the list scrubs attachments on inbound mail.
Alex
Given Calc where DATE(YYYY,MM,DD) and WEEKDAY(X) results 1 = Sunday, 2 =
Monday ... 6 = Friday, 7 = Saturday.
Solve for the first week of the month (WD = 1)
Variables
YYYY = 2017 desired year,
MM = 10 = desired month,
d1 = 1 = First day of the month
WC = 3 = Weekday code for Tuesday
WD = 1 = Week Desired = (1 First week, 2 Second week {maximum of week 4})
WO = WD*7-6 = Week Desired Offset
Solve for
DD = desired day
:. Find the weekday for the first day of the month
X =WEEKDAY(DATE(YYYY,MM,d1))
:. Find the Desired Day
DD =IF(WD-X+1>0,WD-X+WO,WD-X+WO+7)
Proof = WEEKDAY(DATE(YYYY,MM,DD)) = 3
First off, thanks to everyone who did respond! It has been very educational seeing different approaches to this same problem.
Mr Thurgood's reference to that online book is helpful to say the least. Thanks!
The approach Mr Faye offers gives me some insight into things I never tried. (New toys!)
The method Mr Adams offered seems to fit my needs the best (for this purpose) and I've tried it but find that somethings not quite right.
Here's a link to a screen shot of the calculations:
https://www.dropbox.com/s/b4x50sv2e3ycyxa/mytest-spreadsheet.jpg?dl=0
And this is the spreadsheet I built it from:
https://www.dropbox.com/s/mt84id1mg1qkh8h/mytest-spreadsheet.ods?dl=0
In short, I expected for 2017 that the first Tuesday (#3) of Jan should be 1/3/2017 but I get Sunday Jan/1/2017
I'm trying to suss it out but I thought I'd provide that response back.
Again, thanks everyone!
I think you have WC and WD reversed in your formula, but copied the formula that I
had in my spreadsheet, and converted it to a single formula instead of the multiple
cell one. Put this in I7 and then copied it to I8-I18, and it does get the results you
expect.
=DATE($B$1,$A7,1)+$B$2-WEEKDAY(DATE($B$1,$A7,1))+($B$2WEEKDAY(DATE($B$1,$A7,1)))*7+7*($B$3-1)
$b$1 is the year
$a7is the month
$b$2 is the weekday (3 being tuesday)
$b$3 is the week of month (1 to 4, some months could have 5th for some days)
Don't know what WO is, and didn't look at the other stuff.
Don't know if you got the email I sent with the speadsheet, or if it got stripped.
Hi all,
I have a working solution.
First I have a solution by a script in Calc.
I also have a solution by Calc it self.
Both can be found in:
https://drive.google.com/open?id=0B8X24IrD0EquTnY4UWR3Q2F4bWM
<https://drive.google.com/open?id=0B8X24IrD0EquTnY4UWR3Q2F4bWMttp://>
Please change the filename from .xxx in .ods
For those who are interested in the idea behind the solution in Calc it
self: Please make the hidden sheets visible.
The basic idea is create a matrix with 7 columns starting on 1-1
Every next rows gets +7 days Count the order from each occurence within a
month.
Kind regards,
Jan Flikweert
=Seems to be a lot of work with all the sheets.
Year
2017
Month
1
January
=CHOOSE(B2,January,February,=March,April,May,June,;July,August,September,October,"November,December)
Day
1
Sunday
=CHOOSE(B3,Sunday,Monday,ot;Tuesday,Wednesday,Thursday,Fridayuot;,Saturday)
Week
1
First
=CHOOSE(B4,First,Second,t;Third,Fourth,Fifth)
Sunday, January 1, 2017
=DATE(B1,B2,1)+B3-WEEKDAY(DATE(B1,B2,1))+(B3WEEKDAY(DATE(B1,B2,1)))*7+7*(B4-1)
Sunday, January 1, 2017
=DATE(B1,B2,1)+B3-WEEKDAY(DATE(B1,B2,1))+((B3WEEKDAY(DATE(B1,B2,1)))+B4-1)*7
Pasted the cells from spreadsheet, but it might loose all formating for list.
In rows 2-4, added a simple choose option to show the text version of the
Monday, Day, and Week selected.
Four input cells in B1-B4, provide data for single formula in C7 and C8. Set
the date format to show the Day of Week, Monday, Day, Year</div>
Saved CSV version
Row 1: Year,2017,
Row 2:
Month,1,January,=CHOOSE(B2,January,þbruary,March,;April,May,June;,July,August,September,October,November=,December)
Row 3:
Day,1,Sunday,=CHOOSE(B3,Sundayuot;,Monday,Tuesday,=Wednesday,Thursday,Friday,Saturday)
Row 4:
Week,1,First,=CHOOSE(B4,Firstot;,Second,Third,ot;Fourth,Fifth)
Row 5: Just blank
Not sure what messed up the contents, but what I got had a number changes
from the original message?? So, trying again, with just straight text.
In rows 2-4, added a simple choose option to show the text version of the
Monday, Day, and Week selected.
Four input cells in B1-B4, provide data for single formula in C7 and C8. Set
the date format to show the Day of Week, Monday, Day, Year
Saved CSV version
Row 1: Year,2017,
Row 2:
Month,1,January,"=CHOOSE(B2,"January","February","March","Apri","May","
June","July","August","September","October","November","December")
Row 3:
Day,1,Sunday,"=CHOOSE(B3,"Sunday","Monday","Tuesday","Wednesday","
Thursday","Friday","Saturday")
Row 4:
Week,1,First,"=CHOOSE(B4,"First","Second","Third","Fourth","Fifth")
Row 5: Just blank
Hi,
Did you download the file ending on .xxx?
It can be possible the .ods file will download as a excel file without macro
and command button.
Better use .xxx:
https://drive.google.com/file/d/0B8X24IrD0EquTnY4UWR3Q2F4bWM/view?usp=sharing
<https://drive.google.com/file/d/0B8X24IrD0EquTnY4UWR3Q2F4bWM/view?usp=sharing>
When you press the calculate button there appears a second sheet in calc. In
that sheet you can choose in A1 the occurrence off the day in the month with
the automatic filter.
Kind regards,
Jan Flikweert
New to dropbox, so hopefully, the links below will work.
nthweekday.odson Dropbox.
https://www.dropbox.com/l/scl/AACOvRvGCKENXdOpjwXoA40M4DZwc1YhAew
simplenth.odson Dropbox.
https://www.dropbox.com/l/scl/AADOenugbyhmi8kSNxM2l_sT5kuBFhpWNxI
Use in your formulas instead of comma [,] a semi-colomn [;] as separator of
your condition,values
Kind regards,
Jan Flikweert
Hi Michael:
I tried to download your files but it request a permission. As you say you are new in Dropbox I would give you some suggestion:
1) When you tried to share a file, put it in the public folder.
2) There generate a share link into it folder specific the file you want to share.
3) Copy the link generated and paste into the e-mails as you did now.
I hope this help,
Regards,
Jorge Rodríguez
Dropbox didn't seem to work. Put the files there, but then sent a link to my
other email account, and it worked for that, but seems to be specific to users.
Looked, and it says the just changed the public folders, and they are no
longer available?? Also, looked at google drive, and it seems to be the same
way.
So, just make a directory, on my ftp server, and placed the files there.
This is my home machine, so files are there.
The files are like 20K and 10K, so are tiny.
Did modify the nthweekday.ods a little. Added conditional formating for the
second part where formula is a single cell process. Top part has formula over
a couple cells. If one selects 5th week, some of the days will not have a 5th
one in the month, so those are conditioned with the error color scheme.
ftp://setzco.dyndns.org/libreoffice/nthweekday.ods
ftp://setzco.dyndns.org/libreoffice/simplenth.ods
Thank you Michael, I already download your files,
Regards,
Jorge Rodríguez