Calculating the Nth weekday of a month.

classic Classic list List threaded Threaded
23 messages Options
Next » 12
Michael Tiernan Michael Tiernan
Reply | Threaded
Open this post in threaded view
|

Calculating the Nth weekday of a month.

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.

--
To unsubscribe e-mail to: [hidden email]
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
Cley Faye Cley Faye
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

2017-09-28 14:31 GMT+02:00 Michael Tiernan <[hidden email]>:

> 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.

--
To unsubscribe e-mail to: [hidden email]
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
Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

In reply to this post by Michael Tiernan
Le 28/09/2017 à 14:31, Michael Tiernan a écrit :

Hi Michael,

I started from this :

http://www.dummies.com/software/microsoft-office/excel/calculate-the-date-of-the-nth-weekday-of-the-month-in-excel/

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



--
To unsubscribe e-mail to: [hidden email]
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
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

In reply to this post by Michael Tiernan
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
     Date:  29 Sep 2017, 0:36
     Size:  14416 bytes.
     Type:  Libre-sheet

--
To unsubscribe e-mail to: [hidden email]
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

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

In reply to this post by Michael Tiernan
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




On 28 Sep 2017 at 8:31, Michael Tiernan wrote:


From:Michael [hidden email]
To:[hidden email]
Subject:[libreoffice-users] Calculating the Nth weekday of
a month.
Date sent:Thu, 28 Sep 2017 08:31:10 -0400


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.


--
To unsubscribe e-mail to:
[hidden email]
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




Attachments: Z:\home\msetzerii\nthweekday.ods


--
To unsubscribe e-mail to: [hidden email]
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

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

In reply to this post by Michael Tiernan
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
     Date:  29 Sep 2017, 0:52
     Size:  103678 bytes.
     Type:  Unknown

--
To unsubscribe e-mail to: [hidden email]
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

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

In reply to this post by Michael Tiernan
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)



On 28 Sep 2017 at 8:31, Michael Tiernan wrote:

From:           Michael Tiernan <[hidden email]>
To:             [hidden email]
Subject:         [libreoffice-users] Calculating the Nth weekday of
a month.
Date sent:       Thu, 28 Sep 2017 08:31:10 -0400

> 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.
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 http://www.guam.net/home/mikes
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS

ABC          16613838.513356 | EINSTEIN    140253615.999240
ROSETTA      63899283.775010 | SETI        109110480.313016


--
To unsubscribe e-mail to: [hidden email]
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

Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

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: [hidden email]
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
Mike Adams Mike Adams
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

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

On 29 September 2017 at 05:09, Alexander Thurgood <[hidden email]>
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: [hidden email]
> 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: [hidden email]
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
Michael Tiernan Michael Tiernan
Reply | Threaded
Open this post in threaded view
|

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: [hidden email]
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
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

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 [hidden email]
Subject:Re: [libreoffice-users] Re: Calculating the Nth
weekday of a month.
To:[hidden email]
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: [hidden email]
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: [hidden email]
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

Jan Flikweert Jan Flikweert
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

In reply to this post by Michael Tiernan
CONTENTS DELETED
The author has deleted this message.
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

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,&quot;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
,,,


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 [hidden email]
To:;[hidden email]
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: [hidden email]
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: [hidden email]
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

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

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<WEEKDAY(DATE(B
1,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


Note sure how changed the text. Just Months of year, and days of week for
text display

Wish list would allow for attachments. Have another one that uses a table
format, but uses the same kind of formula.

+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 http://www.guam.net/home/mikes
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS

ABC          16613838.513356 | EINSTEIN    140296137.999240
ROSETTA      64023631.925288 | SETI        109144594.967005


--
To unsubscribe e-mail to: [hidden email]
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

Jan Flikweert Jan Flikweert
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

CONTENTS DELETED
The author has deleted this message.
Jan Flikweert Jan Flikweert
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

In reply to this post by Michael D. Setzer II
CONTENTS DELETED
The author has deleted this message.
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculating the Nth weekday of a month.

In reply to this post by 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




--
To unsubscribe e-mail to: [hidden email]
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

jorge Rodríguez Fonseca jorge Rodríguez Fonseca
Reply | Threaded
Open this post in threaded view
|

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: [hidden email]
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

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

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: [hidden email]
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

jorge Rodríguez Fonseca jorge Rodríguez Fonseca
Reply | Threaded
Open this post in threaded view
|

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: [hidden email]
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

Next » 12