VBA - Work out How Many Days are in a Month

[NateBuckley]

Just wondering if anyone has any idea how to find out how many days are in a
given month from a given year?

So If I were to put the month as APRIL 2008 I'd get back 30.

Thanks in advance!

 

[Mike H]

With a date in a1 try

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

Mike

 

[Ivyleaf]

Credit to Ron Rosenfeld for this one... Here is a VBA implementation
of his worksheet function:

Sub daysinMth()
Dim WhichDate As Date
Dim Days As Integer

WhichDate = DateSerial(2008, 2, 12)

Days = 32 - Day(WhichDate - Day(WhichDate) + 32)

MsgBox Days & " in " & WhichDate
End Sub

PS. You did say you wanted a VBA version?

Cheers,
Ivan.

 

[Mike H]

Ah you said VBA

Public Function DaysInMonth(myMonth As Long) As Long
DaysInMonth = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1)
End Function

Call with =DaysInMonth(Month(a1))
with a date in A1

Mike

 

[Ivyleaf]

Ah you said VBA

Public Function DaysInMonth(myMonth As Long) As Long
    DaysInMonth = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1)
End Function

Call with =DaysInMonth(Month(a1))
with a date in A1

Mike







- Show quoted text -

Hi,

Jusa another variation... If we read your post literally, this version
of the function should handle a literal string as well. Actually, it
should handle pretty much anything. If you feed it something that
can't possibly be recognised as some sort of date, it will just give a
#VALUE error:

Function daysinMth(MonthYr As Variant)

If VarType(MonthYr) = vbString Then _
MonthYr = DateValue("1 " & MonthYr)

daysinMth = 32 - Day(MonthYr - Day(MonthYr) + 32)

End Function

Cheers,
Ivan.

 

[NateBuckley]

Thanks mate, both worked a treat, I shall have a play around.

Thank you.

 

[NateBuckley]

Cheers matey I shall check it out now.

Thanks!

Ah you said VBA

Public Function DaysInMonth(myMonth As Long) As Long
DaysInMonth = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1)
End Function

Call with =DaysInMonth(Month(a1))
with a date in A1

Mike