Weekdays in a Month

If you don't mind using a long formula, then this has the slight advantage that it is so easy to adjust the relevant day of the week, and does not need a helper column.


=SUMPRODUCT(N(TEXT(ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&EOMONTH(DATE(YEAR(A1),MONTH(A1),1),0))),"ddd")="Tue"))

The Tip asks how many times a particular weekday occurs within a given month.
My Excel Toolbox includes the following function to return the date of the last weekday of a given month:
=LastWeekdayOfMonth(DayNbr,MonthNbr,YearNbr)
where DayNbr is 1 for Sun, 2 for Mon, ..., MonthNbr is 1 for Jan, 2 for Feb, ..., and YearNbr is a four digit year greater than 1899. For example, the last Sunday in December 2022 is LastWeekdayOfMonth(1,12,2022), which is Christmas Day.
The following LET function formula (Excel 365 and 2021+) is equivalent to LastWeekdayOfMonth:
=LET(EoM,EOMONTH(DATE(YearNbr,MonthNbr,1),0),EoM+1-WEEKDAY(EoM+1-DayNbr))
Combined with Gavin's formula (see comment below), here is the number of times a particular DayNbr occurs within a given MonthNbr and YearNbr:
=CEILING(DAY(LastWeekdayOfMonth(DayNbr,MonthNbr,YearNbr))/7,1)
And here is the equivalent formula using LET:
=LET(EoM,EOMONTH(DATE(YearNbr,MonthNbr,1),0),CEILING(DAY(EoM+1-WEEKDAY(EoM+1-DayNbr))/7,1))
See https://sites.google.com/view/MyExcelToolbox

Hi

Many thanks for your help. One of my friends who did maths looked at my problem and suggested:

=CEILING(DAY(A1)/7,1)

This seems to work, don't know why I never thought of it.


Kind Regards Gavin Holt

Gavin Holt

I knew that I'd left something out! The formulas refer to Table. Once the formulas for the lookp list where completed I selected cell in the list and Pressed Ctrl + t. This convertrs the ddata to a table.

Peter

Gavin Holt
You can make a table of dates together with its weekday and week number in adjacent columns. Use a cell to enter the first date of the month I used D2.

In C5:E5 type Dates,, Weekday, Week.
in C6 type = D3, D6 type =Weekday(c6), in E6 type =COUNTIF($D$6:D6,D6)
In C7 type = C6 + 1 and drag this down to C38 this willl have 31 days.

In G3 I entered my Lookup Date - the one to link the formulas to;

Before entering the lookup formulas we need to tweek the end formulas to account for month with less than 31 days.

In D34 change the formula to =IF(C34<=EOMONTH($C$3,0),WEEKDAY(C34),"Next Month") and copy this down.
In E34 change the formula to =IF(ISNUMBER(D34),COUNTIF($D$6:D34,D34),"") and copy this down

Lookup formulas
=VLOOKUP($G$3,Table2,2)&", "&VLOOKUP($G$3,Table2,3)
="Row "&VLOOKUP($G$3,Table2,2)&", Column "&VLOOKUP($G$3,Table2,3)

Hint: if you set up yoour stuff like mine, you can more it around later to a more ideal format for your needs
(see Figure 1 below)
HTH
Peter

Hi,

I am not very good at Excel. In the NHS (UK) we often schedule activity by looking at the columns on a calendar (e.g. All day theatre list Mondays week 1,3,5). This means on the first Monday, third Monday and if present fifth Monday.

When planning, it would be great to have a formula that would accept a date, and return the ordinal of the weekday for that month:

e.g. Jan 2nd 2023 = 1, Jan 28th 2023 = 4, Jan 29 2023 = 5, Jan 30 2023 = 5

Any help gratefully received

Kind Regards Gavin Holt

Darlene
You can convert the months to dates with the following macro - then you can sort them. The macro formats them as text. In future I'd enter a date and format it as "mmm" for short month or "mmmm" for full month.

Sub Txt2Date()
Dim mnth As Integer, yr As Integer
Dim c As Range, x As Variant, i As Integer

x = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
yr = Year(Now())
For Each c In Selection
For i = LBound(x) To UBound(x)
If LCase(Left(c, 3)) = LCase(x(i)) Then
c.value = DateSerial(yr, i + 1, 1)
End If
Next i
Next
Selection.NumberFormat = "mmmm"
End Sub

(see Figure 1 below)

Hello

Can anyone here please help me? I have been trying to filter months using the filter feature and so far no luck. I can filter the months but they are all mixed up I require them to be in order like January, February etc. I have to include the words Select all and the current year at the top along with the word Blanks at the end. I'm working with a 2016 version of Excel. Any assistance would be greatly appreciated.

Here is a much shorter alternative for the first formula:

=4+(MONTH(DATE(YEAR(A$1),MONTH(A$1),1)+35-WEEKDAY(DATE(YEAR(A$1),MONTH(A$1),1)-ROW(A1)))=MONTH(A$1))

Note that this formula can be entered anywhere in the sheet and copied down 6 rows provided you adjust the A$1 reference except for ROW(A1) which can be any reference to row 1 such as ROW(H1).

In the third formula the NETWORKDAYS.INT function is used but this is is only introduced in Excel 2010. Here is an alternative for Excel 2007 and below:

=4+(MONTH(DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)-E2)+35)=MONTH(A1))

You don't need a loop in the macro. All you have to do is check if a fifth instance of a particular day is still in the same month. In that case the answer is 5 otherwise it's 4.

Function MonthWeekDays(dDate As Date, iDay As Integer)
If iDay < 1 Or iDay > 7 Then
MonthWeekDays = CVErr(xlErrNum)
Exit Function
End If
dDate = DateSerial(Year(dDate), Month(dDate), 1)
MonthWeekDays = (4 - (Month(dDate - Weekday(dDate - iDay) + 35) = Month(dDate)))
End Function