Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
=Not clear on formula.It seems to be more complex then mine, but does also work a little differently. Yours does seem to give the 4th week instead of the 5th week, if it falls in the next month. =IF(MONTH(DATE(Input.$A$2,1,1)-7+(Input.$C$2*7)-(WEEKDAY(DATE(Input.$A$2,1,1)))+Input.$B$2)1,DATE(Input.$A$2,1,1)-7+(Input.$C$2*7)-(WEEKDAY(DATE(Input.$A$2,1,1)))+Input.$B$2-7,DATE(Input.$A$2,1,1)+(Input.$C$2*7)-(WEEKDAY(DATE(Input.$A$2,1,1)))-7+Input.$B$2) =DATE(Input.$A$2,$A2,1)+Input.$B$2-WEEKDAY(DATE(Input.$A$2,$A2,1))+((Input.$B$2WEEKDAY(DATE(Input.$A$2,$A2,1)))+(input.$C$2-1))*7 My two worksheets. ftp://setzco.dyndns.org/libreoffice/nthweekday.odsftp://setzco.dyndns.org/libreoffice/simplenth.ods On 18 Oct 2017 at 1:24, Jan Flikweert wrote: Date sent:60;Wed, 18 Oct 2017 01:24:06 -0700 (MST) From:60;Jan flikweertjanflikwe...@tele2.nl To:;users@global.libreoffice.org Subject:;[libreoffice-users] Re: Calculating the Nth weekday of a month. Now in Calc with a less complicated formula: https://drive.google.com/open?id=0B8X24IrD0EquMjYyUnRNWEZkb28 and the macro in calc: https://drive.google.com/open?id=0B8X24IrD0EquTnY4UWR3Q2F4bWM Kind regards, Jan Flikweert -- Sent from: http://nabble.documentfoundation.org/Users-f1639498.html -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
Thank you Michael, I already download your files, Regards, Jorge Rodríguez El 16/10/2017 a las 14:25, Michael D. Setzer II escribió: 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 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
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 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
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 El 16/10/2017 a las 10:52, Michael D. Setzer II escribió: 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 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
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 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
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 ,,, Row 6: Just blank ,,, Row 7: C7 has the calculated date, as created. D7 has the actual formula from cell C7 in text format ,,"Sunday, January 1, 2017", "=DATE(B1,B2,1)+B3-WEEKDAY(DATE(B1,B2,1))+(B3
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
=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 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 ,,, Row 6: Just blank ,,, Row 7: C7 has the calculated date, as created. D7 has the actual formula from cell C7 in text format ,,Sunday, January 1, 2017,=DATE(B1,B2,1)+B3-WEEKDAY(DATE(B1,B2,1))+(B3WEEKDAY(DATE(B1,B2,1)))*7+7*(B4-1) Row 8: Same as Row 7, but slight difference in formula. ,,Sunday, January 1, 2017,=DATE(B1,B2,1)+B3-WEEKDAY(DATE(B1,B2,1))+((B3;WEEKDAY(DATE(B1,B2,1)))+B4-1)*7 On 16 Oct 2017 at 5:01, Jan Flikweert wrote: Date sent:60;Mon, 16 Oct 2017 05:01:58 -0700 (MST) From:60;Jan flikweertjanflikwe...@tele2.nl To:;users@global.libreoffice.org Subject:;[libreoffice-users] Re: Calculating the Nth weekday of a month. 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 -- Sent from: http://nabble.documentfoundation.org/Users-f1639498.html -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
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. On 12 Oct 2017 at 10:03, Michael Tiernan wrote: From:Michael tiernanmichael.tier...@gmail.com Subject:Re: [libreoffice-users] Re: Calculating the Nth weekday of a month. To:users@global.libreoffice.org Date sent:Thu, 12 Oct 2017 10:03:09 -0400 On 10/11/17 10:17 PM, Mike Adams wrote: (*snip*) 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! -- MCTMichael C Tiernan.http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
On 10/11/17 10:17 PM, Mike Adams wrote: (*snip*) 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! -- << MCT >> Michael C Tiernan.http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calculating the Nth weekday of a month.
Given Calc where DATE(,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
= 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(,MM,d1))
:. Find the Desired Day
DD =IF(WD-X+1>0,WD-X+WO,WD-X+WO+7)
Proof = WEEKDAY(DATE(,MM,DD)) = 3
On 29 September 2017 at 05:09, Alexander Thurgood
wrote:
> Le 28/09/2017 à 17:02, Michael D. Setzer II a écrit :
>
> 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
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-
> unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
