Count # of Days in a Month

[DF]

Does someone know off hand a function that would count the number of
individual days in a given month? For example, how many Mondays in
May 2007, how many Tuesdays, etc... I've searched the help files and
did not find anything I could use. Thanks in advance for your help.

--DFruge

 

[Guest]

Chip Pearson has the answer

http://www.cpearson.com/excel/holidays.htm

Mike

 

[Guest]

in VBA (demo'd from the immediate window)

? Day(DateSerial(2007,05+1,0))
31

in excel

=Day(Date(2007,05+1,0))

 

[merjet]

http://groups.google.com/group/micr...days+in+a+month&rnum=1&hl=en#7a4cf65dfbed8dea

Hth,
Merjet

 

[merjet]

http://groups.google.com/group/micr...days+in+a+month&rnum=1&hl=en#7a4cf65dfbed8dea

Hth,
Merjet

 

[Guest]

With
A1: (any date)
B1: (a number from 1-7, inclusive)......1:Sun, 2:Mon.....7:Sat

This formula returns the count of the referenced day that are in the month
containing the date in A1

C1:
=SUM(INT(((WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)-B1)+(DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-1))/7)))

or....alternatively
C1:
=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,DATE(YEAR(A1),MONTH(A1),1)):INDEX(A:A,DATE(YEAR(A1),MONTH(A1)+1,0))))=B1))

If
A1 contains 5/15/2007
B1 contains 3 (Tue)
then the formula returns 5 (there are 5 Tuesdays in May 2007)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[okrob]

Does someone know off hand a function that would count the number of
individual days in a given month? For example, how many Mondays in
May 2007, how many Tuesdays, etc... I've searched the help files and
did not find anything I could use. Thanks in advance for your help.

--DFruge

And another way to do it is the following formulas for each of the
days...

Mondays =4+(DAY("5-1-2007"-DAY("5-1-2007")+CHOOSE(WEEKDAY("5-1-2007"-
DAY("5-1-2007")),1,7,6,5,4,3,2)+28)>28)

Tuesdays =4+(DAY("5-1-2007"-DAY("5-1-2007")+CHOOSE(WEEKDAY("5-1-2007"-
DAY("5-1-2007")),2,1,7,6,5,4,3)+28)>28)

Wednesdays =4+(DAY("5-1-2007"-DAY("5-1-2007")
+CHOOSE(WEEKDAY("5-1-2007"-DAY("5-1-2007")),3,2,1,7,6,5,4)+28)>28)

Thursdays =4+(DAY("5-1-2007"-DAY("5-1-2007")+CHOOSE(WEEKDAY("5-1-2007"-
DAY("5-1-2007")),4,3,2,1,7,6,5)+28)>28)

Fridays =4+(DAY("5-1-2007"-DAY("5-1-2007")+CHOOSE(WEEKDAY("5-1-2007"-
DAY("5-1-2007")),5,4,3,2,1,7,6)+28)>28)

Saturdays =4+(DAY("5-1-2007"-DAY("5-1-2007")+CHOOSE(WEEKDAY("5-1-2007"-
DAY("5-1-2007")),6,5,4,3,2,1,7)+28)>28)

Sundays =4+(DAY("5-1-2007"-DAY("5-1-2007")+CHOOSE(WEEKDAY("5-1-2007"-
DAY("5-1-2007")),7,6,5,4,3,2,1)+28)>28)

 

[Dave Miller]

DFruge,

This will work for you:

Regards,
David Miller

Function HowManyMondaysInMay2007()
Debug.Print GetDaysInMonth("Monday", "05/01/07")
End Function

Function GetDaysInMonth(sDay As String, dtm As Date) As Long
Dim DaysInMonth As Long, _
i As Integer

DaysInMonth = Day(DateSerial(Year(dtm), Month(dtm) + 1, 0))

For i = 1 To DaysInMonth
If WeekdayName(Weekday(dtm + i)) = sDay Then
GetDaysInMonth = GetDaysInMonth + 1
End If
Next
End Function

 

[Ron Rosenfeld]

Does someone know off hand a function that would count the number of
individual days in a given month? For example, how many Mondays in
May 2007, how many Tuesdays, etc... I've searched the help files and
did not find anything I could use. Thanks in advance for your help.

--DFruge

=4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+DOW))+35)>7)

Where DOW is the Day Of the Week with
Monday = 1
Tuesday = 2
etc.

Or, in VBA, something like:

========================================
Option Explicit
Sub WDinMonth()
Dim DOW As Long
Dim Dt As Date

Dt = DateValue(InputBox("Enter Month and Year"))
Debug.Print "Number of Weekdays in " & Format(Dt, "mmm yyyy")

For DOW = 1 To 7
Debug.Print _
Application.WorksheetFunction.Choose(DOW, _
"Monday", "Tuesday", "Wednesday", "Thursday", _
"Friday", "Saturday", "Sunday"), _
5 + (Day(Dt - Day(Dt) + 1 - Weekday(Dt - Day(Dt + DOW)) + 35) < 7)
Next DOW

End Sub
====================================

Note that in VBA the comparison in the formula is reversed because, in VBA,
TRUE = -1 and FALSE = 0


--ron