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.
[Solved] Calculate date for second Tuesday of month
[Solved] Calculate date for second Tuesday of month
Last edited by dymaedh on Wed Dec 22, 2010 2:13 am, edited 1 time in total.
OpenOffice 3.2.1
Win XP SP2
Win XP SP2
Re: Help needed: Calculate date for second Tuesday of month
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Help needed: Calculate date for second Tuesday of month
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.
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Help needed: Calculate date for second Tuesday of month
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)
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)
OpenOffice 3.2.1
Win XP SP2
Win XP SP2
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: [Solved] Calculate date for second Tuesday of month
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) .
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) .
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: [Solved] Calculate date for second Tuesday of month
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!!
P.S.
Being a WI guy myself, Go Badgers!!
OpenOffice 3.2.1
Win XP SP2
Win XP SP2
Re: [Solved] Calculate date for second Tuesday of month
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.
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.
MacBook Pro (Retina, 15-inch, Mid 2015), macOS Mojave 10.14.3, Apache OpenOffice 4.1.6, Legacy Java 6
Re: [Solved] Calculate date for second Tuesday of month
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.
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.
OOo 3.0.X on Ubuntu 8.x + Windows XP