Number of Days in Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code:

StartDate = (((Now() - Day(Now())) - Format(Now() - Day(Now()), "d")))
EndDate = (Now() - Day(Now()))
DayCount = 0

Do While StartDate < EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
DayCount = DayCount
Case Is = 2, 3, 4, 5, 6
DayCount = DayCount + 1
End Select
StartDate = StartDate + 1
msgbox (DayCount & " " & StartDate)
Loop


Can someone tell me why May 2, 2005 would come back as 0 and how to fix?

Thanks
 
Brian,
It looks like you are trying to do math with data data types. I can't
figure out exactly what you are trying to accomplish. Please describe your
objective, and I think I can help. I have several date calculation routines
and one of them may suit your needs.
 
StartDate = first day of the previous month
EndDate = last day of the previous month

I'm trying to count the number of days (not to include weekends) in the
month. When I run the code I get the following:

5/1/2005 DayCount = 0
5/2/2005 DayCount = 0
5/3/2005 DayCount = 1

and so on. Looking at my calendar, 5/1 is a Sunday, 5/2 is a Monday.

Thanks
 
Brian,
Here is what you need:
EndDate = dateadd("d",-1,dateserial(year(date),month(date),1))
StartDate = dateserial(Year(ld),month(ld),1)

Now, to count the number of non weekend days, the function below will do the
trick. Notice it includes a Holiday table that will exclude Holidays. If
you don't want to exclude Holidays, then you can take out the code that looks
for holidays.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function
 
Brian,

Sorry, I did not finish. I forgot to change some names from my testing.
Where you see ld it is = EndDate, and sd = StartDate.
 
StartDate = first day of the previous month
EndDate = last day of the previous month

I'm trying to count the number of days (not to include weekends) in the
month. When I run the code I get the following:
Click to expand...


Public Function WDinM(SomeYear As Integer, SomeMonth As Integer) As
Integer
Dim wDaysInMonth As Integer
Dim wWeekDaysInMonth As Integer
Dim wDayOfMonth As Integer

wDaysInMonth = Day(DateSerial(SomeYear, SomeMonth + 1, 0))
' every month has at least 4 weeks
wWeekDaysInMonth = 20
' just check the remaining 0 to 3
For wDayOfMonth = 29 To wDaysInMonth
' is it a saturday or sunday?
If (Weekday(DateSerial(SomeYear, SomeMonth, wDayOfMonth)) Mod 7) > 1
Then
wWeekDaysInMonth = wWeekDaysInMonth + 1
End If
Next wDayOfMonth

' and return the total
WDinM = wWeekDaysInMonth

End Function


Hope that helps



Tim F
 
Back
Top