Number Of Days In Month

[mattd4385]

How do i set up a varable (X or whatever) as the number of days in the current month?

Ex. Today is 3-28-11 so... x = 31

 

[Foo_Man_Chu]

This should give you what you're looking for:

dim x as long

x = Day(DateSerial(Year(Date), Month(Now()) + 1, 1) - 1)

hth

 

[Gerald Higgins]

There may be neater ways than this, but . . .
If the date is in A1

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

Edit to add, by the way, mine is obviously a formula solution, not VBA like Foo Man Chu's.

 

[RoryA]

Slightly shorter:
x = Day(DateSerial(Year(Date), Month(Date) + 1, 0))

 

[Expiry]

Or...

=DAY(EOMONTH(A1,0))

If you're using Excel 2003, you'll need to tick the analysis toolpak box under Tools, Addins.

 

[T. Valko]

How do i set up a varable (X or whatever) as the number of days in the current month?

Ex. Today is 3-28-11 so... x = 31

One way...

=DAY(EOMONTH(NOW(),0))

Note that the EOMONTH function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you enter the formula and get a #NAME? error look in Excel help for the EOMONTH function. It'll tell you how to fix the problem.

 

[barry houdini]

Just for fun

If you have a date in A1 this formula will give you the number of days in that month

=42-DAY((A1-DAY(A1)+42))

 

[RoryA]

Barry,
There's something wrong with your brain.