Re: [libreoffice-users] Calculating the Nth weekday of a month.

2017-09-28 14:31 GMT+02:00 Michael Tiernan <michael.tiernan@gmail.com>:

> 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: users+unsubscribe@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