Date: prev next · Thread: first prev next last
2017 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.