Need help to reflect number of days in a month.

L

Lewis Koh

B1= 29/07/2005

B2=IF(MONTH(B1)=1,31,IF(MONTH(B1)=2,28,IF(MONTH(B1)=3,31,IF(MONTH(B1)=4,30,IF(MONTH(B1)=5,31,IF(MONTH(B1)=6,30,IF(MONTH(B1)=7,30,B3)))))))

B3=IF(MONTH(B1)=8,31,IF(MONTH(B1)=9,30,IF(MONTH(B1)=10,31,IF(MONTH(B1)=11,30,31))))

I wanted to get the number of days from B1 but I can't seems to join B
and B3 together. Is this a limitation in excel? Is there a better way t
get the number of days from the month itself
 
R

Ron Coderre

Try this:
=DAY(EOMONTH(B1,0))


Note: If the EOMONTH() function is not available, and returns the
#NAME? error,
Tools>Add-ins...Select Analysis ToolPak...Then click [OK]

Does that help?

Ron
 
L

Lewis Koh

thanks! it works!!! BTW, how is one going to learn abt all these syntax
from fresh? Does the help in excel really helps?
 
B

Bob Phillips

B2: =DAY(DATE(YEAR(B1),MONTH(B1)+1,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Roger Govier

Hi Lewis

There is a limit of 7 nested IF's in a formula, but there are various
workarounds for this if you need them.
Another way to solve your problem though would be
=DATE(YEAR(B1),MONTH(B1)+1,0)-DATE(YEAR(B1),MONTH(B1),1)+1

This relies on the fact that DATE(year,month,0) is equal to the last day of
the previous month.
Therefore, adding 1 to the month in B1 and taking day 0 gives the last day
of the month. Take away the first day of the month nthyen add 1 day to the
result to make the value inclusive of the first and last day.
 
R

Ron Rosenfeld

B1= 29/07/2005

B2=IF(MONTH(B1)=1,31,IF(MONTH(B1)=2,28,IF(MONTH(B1)=3,31,IF(MONTH(B1)=4,30,IF(MONTH(B1)=5,31,IF(MONTH(B1)=6,30,IF(MONTH(B1)=7,30,B3)))))))

B3=IF(MONTH(B1)=8,31,IF(MONTH(B1)=9,30,IF(MONTH(B1)=10,31,IF(MONTH(B1)=11,30,31))))

I wanted to get the number of days from B1 but I can't seems to join B2
and B3 together. Is this a limitation in excel? Is there a better way to
get the number of days from the month itself?
Click to expand...

And another approach, that does not require the Analysis Tool Pak:

=32-DAY(B1-DAY(B1)+32)

(Format the result as General or Number)


--ron
 
B

Bob Phillips

Hi Roger,

I like to avoid the Analysis Toolpak if I can.

Have you seen Ron Rosenfeld's, smart but obtuse :)

Bob


Roger Govier said:
Ron
Both your and Bob's solutions are much neater than my long winded effort.

--
Regards
Roger Govier
"Ron Coderre" <[email protected]>
wrote in message
Try this:
=DAY(EOMONTH(B1,0))


Note: If the EOMONTH() function is not available, and returns the
#NAME? error,
Tools>Add-ins...Select Analysis ToolPak...Then click [OK]

Does that help?

Ron
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Bob
I hadn't, but very smart.
Well done Ron.

--
Regards

Roger Govier


Bob Phillips said:
Hi Roger,

I like to avoid the Analysis Toolpak if I can.

Have you seen Ron Rosenfeld's, smart but obtuse :)

Bob


Roger Govier said:
Ron
Both your and Bob's solutions are much neater than my long winded effort.

--
Regards
Roger Govier
"Ron Coderre" <[email protected]>
wrote in message
Try this:
=DAY(EOMONTH(B1,0))


Note: If the EOMONTH() function is not available, and returns the
#NAME? error,
Tools>Add-ins...Select Analysis ToolPak...Then click [OK]

Does that help?

Ron
Click to expand...
Click to expand...
Click to expand...
 
Top