+ Reply to Thread
Results 1 to 96 of 96

Need help to reflect number of days in a month.

  1. #1
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    253

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

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    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

  3. #3
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    253
    thanks! it works!!! BTW, how is one going to learn abt all these syntax from fresh? Does the help in excel really helps?

  4. #4
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  5. #5
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  6. #6
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  7. #7
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  8. #8
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  9. #9
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  10. #10
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  11. #11
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  12. #12
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  13. #13
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  14. #14
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  15. #15
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  16. #16
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  17. #17
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  18. #18
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  19. #19
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  20. #20
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  21. #21
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  22. #22
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  23. #23
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  24. #24
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  25. #25
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  26. #26
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  27. #27
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  28. #28
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  29. #29
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  30. #30
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  31. #31
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  32. #32
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  33. #33
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  34. #34
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  35. #35
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  36. #36
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  37. #37
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  38. #38
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  39. #39
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  40. #40
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  41. #41
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  42. #42
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  43. #43
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  44. #44
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  45. #45
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  46. #46
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  47. #47
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  48. #48
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  49. #49
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  50. #50
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  51. #51
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  52. #52
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  53. #53
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  54. #54
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  55. #55
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  56. #56
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  57. #57
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  58. #58
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  59. #59
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  60. #60
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  61. #61
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  62. #62
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  63. #63
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  64. #64
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  65. #65
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  66. #66
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  67. #67
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  68. #68
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  69. #69
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  70. #70
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  71. #71
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  72. #72
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  73. #73
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  74. #74
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  75. #75
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  76. #76
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  77. #77
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  78. #78
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  79. #79
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  80. #80
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  81. #81
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  82. #82
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  83. #83
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  84. #84
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  85. #85
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  86. #86
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  87. #87
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  88. #88
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  89. #89
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  90. #90
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

    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
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > 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
    >




  91. #91
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  92. #92
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

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

    --

    HTH

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


    "Lewis Koh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  93. #93
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




  94. #94
    Bob Phillips
    Guest

    Re: Need help to reflect number of days in a month.

    Hi Roger,

    I like to avoid the Analysis Toolpak if I can.

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

    Bob


    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > >
    > > 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
    > >
    > >
    > > --
    > > 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
    > >

    >
    >




  95. #95
    Ron Rosenfeld
    Guest

    Re: Need help to reflect number of days in a month.

    On Fri, 29 Jul 2005 08:52:20 -0500, Lewis Koh
    <[email protected]> wrote:

    >
    >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?


    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

  96. #96
    Roger Govier
    Guest

    Re: Need help to reflect number of days in a month.

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

    --
    Regards

    Roger Govier


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Roger,
    >
    > I like to avoid the Analysis Toolpak if I can.
    >
    > Have you seen Ron Rosenfeld's, smart but obtuse :-)
    >
    > Bob
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >> news:[email protected]...
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > 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
    >> >

    >>
    >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1