Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Month function (Excel 2000 SR1)

    I am doing some sales analysis and need to analyze product sales over time.

    I need to pull just the month out of a cell with the date in it. The dates are formatted as 9/12/2000. If I use =MONTH and point to the cell containing 9/12/2000, the function returns January 1900. Is there any faster way of getting the month besides typing =MONTH("2000,9,12) etc into each cell. I've also thought of changing the date amounts to text and using the =LEFT function to get the month but that is also quite a bit of work. My database has 1300+ rows right now.

    Another question: Can I nest a =AND function into a =COUNTIF function. I've tried and can't get it to work.

    Thanks in advance for any help you might be able to give me.

    Christa

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Month function (Excel 2000 SR1)

    Hi Christa,
    The reason you're getting Jan 1900 is that the month function is returning 9 (i.e. September) but your cell is formatted as a Date. Because Excel calculates dates as numbers starting from 1/1/1900, 9 is equivalent to 1/9/1900, hence you get Jan 1900. If you format your cell as General, you should see 9.
    As regards your COUNTIF and AND question, I presume you mean you want to count cells where more than 1 condition is met? You can do this a variety of ways but we'll need more detail to give you a specific answer.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month function (Excel 2000 SR1)

    I have a database that contains records of product downloads. What I need to do is analyze how the downloads of particular products have changed over time. In database there is a column that contains the date and a column that contains what product was downloaded on that date. I would like to "Count" all the downloads of a particular product during a particular month and eventually chart them....that is why I was trying to use COUNTIF(AND...

    Thanks for the help on the MONTH function...I had formated the column I was pulling from as General but not the column where my MONTH function actually was...once I did that...it worked.

    Thanks again,

    Christa

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Month function (Excel 2000 SR1)

    For something like that I'd suggest a pivot table is probably the easiest thing - you could have the products listed as rows on the left, the months as columns along the top and a simple count as your data. Otherwise you're probably going to need to use array formulae.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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