Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
Chip Pearson has the answer
http://www.cpearson.com/excel/holidays.htm Mike "DF" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
in VBA (demo'd from the immediate window)
? Day(DateSerial(2007,05+1,0)) 31 in excel =Day(Date(2007,05+1,0)) -- Regards, Tom Ogilvy "DF" wrote: 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
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 "DF" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
On May 4, 1:12 pm, DF wrote:
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) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count # of Days in a Month
On 4 May 2007 11:12:35 -0700, DF wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Number of days in month counted from shortened name of month & yea | Excel Worksheet Functions | |||
CountDays? need to count days in range by month | Excel Worksheet Functions | |||
User defined function to count pay days in current month. | Excel Programming | |||
Count number of days in given month? | Excel Worksheet Functions |