total days in the month based on date

[bark01]

Hi, is there a formula that will say how many days there are in a month ?

B9 contains the a refrence to the first day of the month eg 1/7/2008 I want the cell above it (B8) to return 31. If the date reference is 1/6/2008 I want (B8) to return 30.

I'm using it to pro rata by the number of days in a month.

regards

 

[carpetony]

One method I've used before is find the first day of the 'next' month and subtract a day.

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

HTH
Tony

 

[njimack]

Isn't that a slightly less efficient version of the formula I posted?
=DAY(DATE(YEAR(B9),MONTH(B9)+1,0))

One method I've used before is find the first day of the 'next' month and subtract a day.

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

HTH
Tony

 

[DonkeyOte]

moreover it's risky as was pointed out to me the other day by Peter_SSs... the 0 day approach is much safer.

EDIT: to quantify: using DAY in DATE function can be risky...

 

[carpetony]

Sorry been gone. My bad, on all counts.