Finding Number Of Days in a month

C

Charles

Hi I need to find uot how many days are in a month in VBA
is there a way of doing this if so can someone help me
please TIA
Charles
 
F

Frank Kabel

Hi
try
Sub foo()
Dim date_value As Date
date_value = CDate("01.03.2004")
MsgBox Day(CDate(Month(date_value) + 1 & "-" & "1-" &
Year(date_value)) - 1)
End Sub

you may have to adapt the CDate part to your regional date settings
 
L

LEB

-----Original Message-----
Hi I need to find uot how many days are in a month in VBA
is there a way of doing this if so can someone help me
please TIA
Charles
.
Click to expand...
This probably isn't the best way, but you could run a
Do...Until loop. use two variables as counters. Use the
first counter variable, initialized to the value 1, and
run the loop until the variable is greater than 31 days.
Create a text date value from each value of the first
counter variable [Ex: CurrDate=Month(date)&"/" & counter
& "/" & Year(date)]. Then test to see if the date is
valid, using an If...Then test and the IsDate function
[Ex: if IsDate(CurrDate) then...

If the date is valid, increment the second counter to
keep track of the number of days in the month, increment
the first counter to its next value, and repeat the loop.
if the date isn't valid, exit the loop, and the number of
days in the month will be the value of the second
counter.
 
H

Harald Staff

Hi Charles

This is how I'd do it:

Function DaysInMonth(Dt As Date) As Long
DaysInMonth = Day(DateSerial(Year(Dt), _
Month(Dt) + 1, 0))
End Function

Sub test()
Dim L As Long, Dt As Date
For L = 1 To 12
Dt = DateSerial(2004, L, 1)
MsgBox _
Format(Dt, "mmmm yyyy") & " has " & _
DaysInMonth(Dt) & " days"
Next
End Sub
 
R

Rob van Gelder

Sub test()
Dim dtm As Date, lngDays As Long
dtm = "25-Feb-2004"

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

MsgBox lngDays
End Sub
 
Top