[Solved] Determine Nth day-of-week for month

[taylor.r113y]

Hi Open Office,

I am looking for a formula that inputs a number only on every 3rd Saturday of the month.
The date is in column A, and runs the length of the column, from cell A1 to A999.
It is a nested If function that I want to use.
The formula I want used to appear on this page:
https://exceljet.net/formulas/get-nth-d ... k-in-month
But the page itself has been edited.
Any and all help is vastly appreciated.

[MrProgrammer]

3rd Saturday of the month.

A date is the third Saturday of the month if DAY(date)>14, DAY(date)<=21, and WEEKDAY(date;3)=5.

formula that inputs a number

I find this confusing and don't know what you want. A formula cannot "input a number". Formulas return (or output) values.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Calc date formulas, perhaps section N, O, or P.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[karolus]

the sequence of third saturdays for 2026 ( Libreoffice since version 24.8 )

Code: Select all

=DATE( 2026; SEQUENCE(12) ; 21 )-MOD(WEEKDAY(CURRENT());7)

[keme]

To

I am looking for a formula that inputs a number only on every 3rd Saturday of the month.

I assume that you want the formula to output a number at the given condition.
In computer lingo:
Input is taken to mean that the computer/software/model receives data from human action/external process/storage.
Output is when the computer/software/model displays/pushes/saves data towards human/process/storage.

The date is in column A, and runs the length of the column, from cell A1 to A999.

First, find a reference point based on the date in column A. Assuming that we are now on the top row, where date is in cell A1:
The DAY() function returns the number of days passed in the current month.
The CURRENT() function returns the intermediate value calculated by our formula up to "this point".
Let us use the last day of previous month as the first "anchor point". To calculate that date:

Code: Select all

=A1-DAY(CURRENT())

We need to move that anchor point three weeks into the correct month, plus one day for "good measure".

Code: Select all

=A1-DAY(CURRENT())+22

Now we have calculated the 22nd day of the month (from the date in A1). This means that there will be 21 days, or exactly three full weeks of the month (IOW also exactly 3 Saturdays) before this date.
The WEEKDAY() function numbers weekdays starting from sunday=1. Incidently, this number is the same as "count of days since last saturday" (a fact which karolus also utilizes in his answer; I borrowed it from there ). So, to get to the previous Saturday, subtract the number for weekday from this date.
If you will also need this for days other than Saturdays, it may be better to go about it differently.

Code: Select all

=A1-DAY(CURRENT())+22-WEEKDAY(CURRENT())

This will be the third Saturday in the month indicated by the value in A1.
Note that the two occurrences of CURRENT() in this formula will have different values. This may appear confusing; it takes a little practice getting used to it. When you edit a formula containing CURRENT(), make sure that you have complete understanding and control of the calculation sequence.
Your conditional formula could then be:

Code: Select all

=IF((A1-DAY(CURRENT())+22-WEEKDAY(CURRENT()))=A1;A_NUMBER;"")

Replace the A_NUMBER placeholder with the particular number (or reference) you want to output at this point.

[taylor.r113y]

I have two sides of the spreadsheet, the left side (of inputs of numbers), and the right side (showing the outcomes of the result of those inputs).
Thank-you keme and karolus, those are the formulae that I wanted! Thank-you!

[morchat]

The formula proposed by @karolus will not work (as he himself wrote) in Apache OpenOffice. For AOO, use the modified formula given below and validate it with CSE (CTRL+SHIFT+ENTER).

Code: Select all

=DATE( 2026; {1|2|3|4|5|6|7|8|9|10|11|12} ; 21 )-MOD(WEEKDAY(CURRENT());7)

[morchat]

Following the solution shown in the link in the first post, I've prepared a small spreadsheet that specifies the selected day of the week in a specific order. Some days of the week can appear more than four times in a month. In the spreadsheet, I allow the request for a fifth occurrence. Conditional formatting highlights months where this occurrence doesn't occur. I've also added the option to select the last occurrence in the month. The declared month can be specified by entering any date within the month.