Need help to reflect number of days in a month.
 

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(MONT H(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?


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391281

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


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

Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=391281

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


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391281

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

--

HTH

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


"Lewis Koh" wrote
in message ...

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,I
F(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?


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile:
http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391281

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.

--
Regards
Roger Govier
"Lewis Koh" wrote
in message ...

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(MONT H(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?


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile:
http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391281

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

--
Regards
Roger Govier
"Ron Coderre"
wrote in message
...

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


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

Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile:
http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=391281

On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
wrote:


B1= 29/07/2005

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

B3=IF(MONTH(B1)=8,31,IF(MONTH(B1)=9,30,IF(MONTH(B 1)=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?

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

Hi Roger,

I like to avoid the Analysis Toolpak if I can.

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

Bob


"Roger Govier" wrote in message
...
Ron
Both your and Bob's solutions are much neater than my long winded effort.

--
Regards
Roger Govier
"Ron Coderre"
wrote in message
...

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


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

Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile:
http://www.excelforum.com/member.php...o&userid=21419
View this thread:
http://www.excelforum.com/showthread...hreadid=391281

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

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Hi Roger,

I like to avoid the Analysis Toolpak if I can.

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

Bob


"Roger Govier" wrote in message
...
Ron
Both your and Bob's solutions are much neater than my long winded effort.

--
Regards
Roger Govier
"Ron Coderre"
wrote in message
...

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


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

Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile:
http://www.excelforum.com/member.php...o&userid=21419
View this thread:
http://www.excelforum.com/showthread...hreadid=391281