Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula for days in a month (excel 2000)

    Is there a formula that will yield the number of days in a month, with a single date as input?

    Thank you
    Guillermo

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula for days in a month (excel 2000)

    This will yield the number of days in the month of the date given in A1:

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

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula for days in a month (excel 2000)

    Excellent!!

    Know any www site that has related tips on similar subject (excel & dates)?

    Thank you.
    Guillermo

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula for days in a month (excel 2000)

    Chip Pearson has a site with a lot of useful Excel topics and tips

    Here is the part on date and time:
    http://www.cpearson.com/excel/datetime.htm

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: formula for days in a month (excel 2000)

    Guillermo, you mean you don't like the Lounge? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    An excellent site for many Excel topics is (Chip) Pearson Software Consulting, and you'll find several time and date sections within that site in his topic listing.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula for days in a month (excel 2000)

    Lounge is GREAT!!!

    The Lounge beats any source of information about excel and other (several) products. I feel bad for posting questions that are very basic. I should study more!

    Thanks!
    Guillermo

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula for days in a month (excel 2000)

    The only bad question here is one that you don't ask. If you need to ask, then there are probably a lot of others out there that don't know either.
    Legare Coleman

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: formula for days in a month (excel 2000)

    Hi Steve,

    A marginally shorter formula:
    =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: formula for days in a month (excel 2000)

    Or shorter still
    =DAY(EOMONTH(A1,0))

    But you need the analysis toolpak addin.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: formula for days in a month (excel 2000)

    Hi Andrew,

    Good call. As you say, you need the analysis toolpak addin for your solution. I tend to avoid solutions involving add-ins since I can't rely on users having them installed. If that happens, the formula from the add-in ceases to work and the spreadsheet can no longer be relied on to calculate correctly.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula for days in a month (excel 2000)

    I also avoided EOmonth for this reason.

    I also avoided the day being "0". I have seen some squirrley results with it sometimes, so I try to stick with acceptable values and not use trickery to fool the function.

    Steve

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula for days in a month (excel 2000)

    Hi Steve,

    I often use the equivalent of =DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) in Access, and I have never experienced a problem with it.

    If you don't trust such tricks, you should also avoid =DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1), for if the date in A1 is in December, MONTH(A1)+1 evaluates to 13. The 13th month of the year seems just as dangerous (or safe) as the 0th day of the month.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula for days in a month (excel 2000)

    You are right. I just didn't think about the 13th month ("brain fart") or I would have made a more complicated formula. Who wants to be consistent anyway in how we do things?

    I know it works MOST of the time. I have just seen some cases when it had problems (usually with imported spreadsheets).

    Steve

Posting Permissions

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