Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Function For Days In Month (2000)

    Does anyone know if there is a function I can use in a formula that will automatically calculate the days in a month? For example, if I want to multply the results from one cell by the number of days in a given month, do I have to input the number of days of that month, or is there a function that will automatically do it for me?
    Thanks,
    Jeff

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Function For Days In Month (2000)

    Jeff,

    Could you be more specific as to the source of data for the month in qiestion. Will it be from a dates entered into a range of cells on your spreadsheet, or do you intend just using the number of the month ?

    If you have a date in A1 the following formula should tell you the number of days in the relevant month :<pre> =DAY(EOMONTH(A1,0))</pre>

    but only if you have the Analysis Toolpak add-in active. If you do not, try this alternative <pre> =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))</pre>

    To multiply a value in B1 using the first you could use = B1*DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) using the second formula (the same applies for the first)

    Andrew C

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Function For Days In Month (2000)

    Thanks, Andrew. I do have the Analysis Toolpak add-in, and your tip worked like a charm! Next question: Assume you type in cell A1 "11/1/2001" and format the cell to show "November-01". How about a shortcut to then copy down the next 11 cells (i. e., to A12) and have them show the next 11 months? This would save typing in cell A2, "=A1+30" that will result in cell A2 displaying "December-01", in cell A3, "=A2+31" that will result in cell A3 displaying "January-02", etc. This would save eleven manual steps, as well as catch any February Leap Years, etc.
    Thanks again,
    Jeff

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

    Re: Function For Days In Month (2000)

    Select the cell with the date and right click on the fill handle in the lower right corner. Drag the cell down 12 rows and release the right button. In the pop up box select "Fill Months."
    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Function For Days In Month (2000)

    Thanks, Legare. The actual steps I had to follow: Enter the beginning month's date (in my example, "11/01/2001") in cell A1, and format it for a date format such as (using my example) "November-01". Next, left mouse click on A1, and hold left mouse button down and fill down to cell A12 (after which cell A1-A12 will be highlighted, with only cell A1 containing the data previously typed in), left mouse click "Edit", "Format", "Series", "Month".
    Very useful, especially when used in conjenction with the previous tip!
    Thanks again to you both!

Posting Permissions

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