Count Dates Within A Range

[KJernigan]

I used a COUNTIF example from another thread as a guide for my own spreadsheet and cannot return the correct numbers:

This example for returning a count of dates within a range:

=COUNTIF(A:A, ">=1.7.2010")-COUNTIF(A:A,">31.7.2010")

Became in my workbook:

=COUNTIF('Jan 2010'!L:L, ">=Sheet5!B3")-COUNTIF('Jan 2010'!L:L,">Sheet5!C3")

Perhaps I am missing something basic such as date formatting, as I'm just now starting to learn how to use formulas. Can anyone help me with this formula in E3, Sheet 5? (please see attached file "CY 2010 to CY 2011")

Best Wishes,

Keith

[zbor]

When you use "" that mean you dealing with text values..

To deal with references you must use them out of ""

=COUNTIF('Jan 2010'!L:L, ">="&Sheet5!B3)-COUNTIF('Jan 2010'!L:L, ">"&Sheet5!C3)


try use this solution because I have problem with dates so I can not test it.

[KJernigan]

The value returned is 0 so I must have something misformatted or a similar problem. I have attached my updates which you suggested in case someone can test it. Thanks for your help!

[arthurbr]

Following up on zbor, in col L (sheet jan 2010) your dates are text looking like dates, not real dates

Select this column - Go to data - text to columns - Click "Next" twice In the Column data format box select "date" and "MDY' in the dropdown box. Finish Your dates will now move to the right of the cell and if you have followed zbor's advice the results will come up.

If MDY does not work you can try other combinations

[KJernigan]

That is the ticket! Thank you BOTH very much! The data is imported from a Crystal Reports file where it is converted to Excel worksheet, so that is probably why it is text and not a true date.

Thanks again! I have attached the updated workbook.

Best Wishes,

Keith