Page 1 of 2 12 LastLast
Results 1 to 10 of 12
Like Tree2Likes

Number of days in a month

This is a discussion on Number of days in a month within the Excel Questions forums, part of the Question Forums category; The function WEEKDAY will tell me the day of the week that a date falls on. How can I determine ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72

    Default

    The function WEEKDAY will tell me the day of the week that a date falls on.

    How can I determine the number of days in the month of that date?

    In cell A2, I enter a date, say 1/1/2003 and I want to know the number of days in January.

    (I know, I can look on a calendar, but I'd like some sort of automation. Next year is a Leap Year and I'd like the calendar I am building to know that.)

    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    =DAY(DATE(YEAR(A2),MONTH(A2)+1,))

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72

    Default

    Mark,

    That works, but I don't BEGIN to understand why. Would you walk me through it so I know what's going on?

    Thanks!

  4. #4
    Board Regular
    Join Date
    May 2002
    Posts
    541

    Default

    The code above works like this:

    In some cell, you have a date. Let's say it's A1. This date can be broken down into it's components (day, month, year) by using the built-in excel functions:

    DAY(), YEAR(), MONTH().

    Also, excel makes available the function called "DATE()" to build a date, if you have the three components. If you placed:

    =DATE(2003, 1, 2)

    in a cell on your worksheet, that cell would then become the date "January 2nd, 2003", depending on how you formatted it.

    So, you can construct dates out of three components, using the DATE function.

    This is all pulled together in the example given above, when you say:

    =DATE(YEAR(A1), MONTH(A1) + 1, 0)

    You're effectively saying:

    * The year is the same as the YEAR() in cell A1.

    * The month is one more than the month in cell A1.

    * The day is zero.

    Think about that last bit. What's January 0th, 2002?

    To excel, the zero-th day of a month, is the last day of the PREVIOUS month. So January 0, 2002, is actually December 31, 2001.

    After all that, you surround the entire formula with the DAY() function, in order to pull out just the 2 digit day. In the case of January 0, 2002, you'll pull out the 31 from December.



    I've struggled with this same issue for some time. Calculating the end of the month is something we do quite often here with our spreadsheets, so here's a function that you can place in your VB project, so that you can just type:

    =EndOfMonth(A1)

    In a cell, and if A1 is a date, you'll get the last day of that date.

    Place this code in a new module:

    Function EndOfMonth(mDate As Date) As Variant

    EndOfMonth = Day(DateSerial(Year(mDate), Month(mDate) + 1, 0))

    End Function


    _________________


    [ This Message was edited by: OdinsDream on 2003-01-06 14:09 ]
    srizki and jeng02 like this.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    The DATE worksheet function returns the datevalue for a given set of arguments that include year, month, day. If day is 0 or omitted DATE returns the datevalue for the last day of the preceeding month. So, I added 1 to the month and omitted the 3rd argument to get the datevalue for 1/31/2002. The DAY function converts a datevalue to an integer between 1 and 31 representing the day portion of the date.

    [ This Message was edited by: Mark W. on 2003-01-06 14:11 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    There's no reason to write VBA code for a EOM function since this function already exists in the Analysis ToolPak...

    =DAY(EOMONTH(A2,0))

    I recommended DAY(DATE(...)) only to avoid the use of thec Analysis ToolPak!

    [ This Message was edited by: Mark W. on 2003-01-06 14:15 ]

  7. #7
    Board Regular
    Join Date
    May 2002
    Posts
    541

    Default

    Well, everybody has their own preference. Since our workbooks are distributed to many different machines, we can't control whether the Analysis ToolPak is installed or not.

    We also calculate the End-of-Month within many VB macros in the workbooks, so it's just easier to call the function.

    to each his own.
    <form action="http://www.google.com/search" name=f><input style="border:1px solid;" maxLength=256 size=15 name=q value=""> <input type=submit value="Search" name=btnG></form>

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    ...which is why I recommended...

    =DAY(DATE(YEAR(A2),MONTH(A2)+1,))

    If one would like to hide the complexity of this formulation... It's easy enough to create a defined name, EndOfMonth, that refers to =DAY(DATE(YEAR(A2),MONTH(A2)+1,)).

    [ This Message was edited by: Mark W. on 2003-01-06 14:42 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72

    Default

    Thanks for the explanation. My thought process was working on a max function and getting nowhere!

    The date of zero taking you back a month was the critical missing information, although the truth is I'd have been hard pressed to USE that information.

    Thanks for the formula AND the lesson!

  10. #10
    New Member
    Join Date
    Aug 2008
    Posts
    8

    Default Re: Number of days in a month

    Mark...Superb work....

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com