Month function (Excel 2000 SR1)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote