[Solved] Calculate date for second Tuesday of month

[dymaedh]

I am looking to calculate the date for the second Tuesday (for example) of any given month. I have searched online and in these forums for the way to do this but I cannot seem to refine a search well enough to get the information I am after.

I appreciate any help given!

Regards.

[RoryOF]

Look at
http://wiki.services.openoffice.org/wik ... _functions
for date/time functions.

[MrProgrammer]

Hi, and welcome to the forum.

The Kth Zday of MM in YYYY where Z:Sun=1 Mon=7 Tue=6 Wed=5 Thu=4 Fri=3 Sat=2 is =DATE(YYYY;MM;7*K)-WEEKDAY(DATE(YYYY;MM;Z);3). So the second Tuesday of this month is =DATE(2010;12;14)-WEEKDAY(DATE(2010;12;6);3).

The last Zday of MM in YYYY is a week before the the first Zday of MM+1 in YYYY. It doesn't matter if MM is 12 since DATE(YYYY;13;…) is valid and means January of the following year.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[dymaedh]

MrProgrammer, Thank you for your explanation!

I was looking to chart specific days of the month for certain tasks but did not want to have to edit each sheet of tasks. The first sheet in my .ODS is names "Key" in which I edit field B2 each month to MM/01/YYYY (MM being any given month, YYYY being any given year). It is the only field in the spreadsheet that I edit, using a wide variety of date and time functions to schedule many tasks during said month.

I have altered your equation as such to fit my needs and thank you for your assistance!


Here is what I used in this particular instance in case anyone else was interested:
=DATE(YEAR(Key.B2);MONTH(Key.B2);7*2)-WEEKDAY(DATE(YEAR(Key.B2);MONTH(Key.B2);6);3)

[MrProgrammer]

Well, if you have a cell which contains the date of the first of the month, you can also use =Key.B2+13-WEEKDAY(Key.B2+5;3). Adding 13 and 5 gives to B2 gives the 14th and 6th that are needed by the formula for the second Tuesday.

Since B2 represents a month, you may want to format it (Format, Cells, Number, Format code) as MMM YYYY so that the first of this month would be displayed as "Dec 2010" (no day of month). I've found that for my locale, I can enter a date the date MM/01/YYYY as simply MM/YYYY (like 12/2010) .

[dymaedh]

Even better. Nobody likes a long winded expression if it isn't necessary. Big help, thank you.

P.S.
Being a WI guy myself, Go Badgers!!

[AndyNoCo]

After you use MrProgrammer's formula to determine the first occurrence of the date of the Nth dayname of a month (let's say it's in cell A1), this formula looks like it works for calculating the dates of the Nth daynames in following months.

In cell A2: = ( EOMONTH ( A1 ; 0 ) + 7 [days in a week] * [desired week number, 1-4] ) - WEEKDAY
( DATE ( YEAR ( EOMONTH ( A1 ; 1 ) ) ; MONTH (EOMONTH ( A1 ; 1 ) ) ; WEEKDAY ( A1 ) + 1 ) ; 3 )

Do not use absolute references (like "$A$1"). Then select cell A2 through A99 or whatever, and "fill down". This should give you the date of the Nth dayname for however many more months you want.

[jimavera]

An alternative solution which uses the standard representation for day-of-week (1-7 for Sun-Sat)
and which can be directly translated to analogous BASIC macro code is:

The Kth Wday of MM in YYYY where W: Sun=1 Mon=2 Tue=3 Wed=4 Thu=5 Fri=6 Sat=7 is

=DATE(YYYY;MM;7*K) - WEEKDAY(DATE(YYYY;MM;8-W);1) + 1 ' (3rd arg ;1 may be omitted)

and equivalent BASIC macro code is

DateSerial(year,month,7*k) - WeekDay(DateSerial(year,month,8-W)) + 1

A demo/test spreadsheet is at http://abhweb.org/downloads/LibreOpenOf ... fMonth.ods

The main answer from MrProgrammer can not be directly translated to Basic macro code because it depends on the optional third argument to WEEKDAY (3) which is not available in the analogous Basic function WeekDay.