MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How To Calculate the 3rd Wednesday Of Each Month In Numbers?

I’m retired and on Social Security, I watched one of your latest videos on Numbers and I’m trying to create an expense/budget spreadsheet. My SS check comes in on the third Wednesday each month. How would I calculate the 3rd Wednesday for any given month.

—–
Dave Karp

Comments: 2 Responses to “How To Calculate the 3rd Wednesday Of Each Month In Numbers?”

    3 years ago

    It is very tricky, but it can be done. I'll assume that the month is in cell B2, as an example. It should be there represented by the first day of the month, so 12/1/2020. But if you type 12/2020 or December 2020, you'll get that date, but formatted without the day.

    Then the formula in B3 to calculate the date of the third Wednesday is:

    B2−WEEKDAY(B2)+4+3×7−IF(WEEKDAY(B2)>4,0,7)

    Let me break that down. B2 is the first day of the month. So WEEKDAY(B2) gives you the day of the week for the first. A 1 would be a Sunday, so a 4 would be Wednesday.

    Then add 4, since we want this to figure out a Wednesday.
    Then add 3x7 since we want the third Wednesday.

    Now if the first of the month happens to be earlier in the week than Wednesday, then we'll get the right number for the third Wednesday. But if the first is on a Thursday or later, this will overshoot by a week. So the IF statement will check for that and subtract 0 or 7, the 7 would be if the first is late in the week.

    And there you go. Since the initial value, the B2, is a date, and the rest is a calculated number, you'll get a date as the result, like 12/16/2020.

    Dave Karp
    3 years ago

    Thanks Gary, works just as you said.

Comments Closed.