Number of dates falling in a date range

[Cralis]

Hi guys,

I'm pretty new to Excel, but am trying to do something.

I know about NETWORKDAYS. The query I have is different, I think.

I have a cell holding Start Date, and a cell holding End Date.

On another sheet, I have a column holding specific dates. For example,
public holidays.

Is there a way to get the number of these public holidays that fall
within my start/end date?

For example:

Start Date: 1st Jan, 2009
End Date: 10th Jan, 2009

Exception Dates:
4th Jan
8th Jan
12th Jan

Based on that data, I need to report '2' as the answer, as 4th and 8th
fall inside my range. Is there a formula or something I can use to
calculate this?

 

[T. Valko]

As long as *all* of your dates are true Excel dates. Try this:

A1 = start date
B1 = end date

Sheet2 A1:A3 = exception dates

=COUNTIF(Sheet2!A1:A3,">="&A1)-COUNTIF(Sheet2!A1:A3,">"&B1)

 

[Cralis]

Thanks Biff,

I'm not getting the correct result.

This is what I have tried, based on your assistance.

=COUNTIF( 'Public Holidays'!$B$4:$B$400,">="&B5)-COUNTIF( 'Public
Holidays'!$B$4:$B$400,">"&C5)

The holidays are in a column on 'Public Holidays' spreadsheet.

The cell (for this row) that has the start date is B5, with C5 holding
the End Date.
I have Zero display switched off, and with that, this is returning no
value. Have I done something wrong?

 

[T. Valko]

I have Zero display switched off, and with that, this is returning no

value.

Turn it back on (at least temporarily) and see if the formula returns 0. If
it does then I suspect that your dates are not true Excel dates.

The example dates you posted are not true Excel dates.

Start Date: 1st Jan, 2009
End Date: 10th Jan, 2009
Exception Dates:
4th Jan
8th Jan
12th Jan

 

[Cralis]

Biff!
My fault. It's infact working. It was showing some strange error, but
when I copied the forumula to the rest of the rows, the error
vanished. So, thanks very much! Works likem a charm. Now, just need to
work out what it's doing!

Thanks,
Craig

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[Cralis]

I'm not 100% why I was getting the error. What happened was I created
the formula, which had a logic error. That is, it never displayed an
error, but the output was unexpected. I copied that formula to all the
rows below...

When I edit a rows forumla with your correct version, it gave the
error.. something about an inconsistant formula. I then copied that
formula to all the rows again, and it was fine. Was it reporting that
this row (that I had just edited) wasn't the same as all the other
rows, and because I had copied them... Excel was just warning me that
it wasn't the same as all the copied cells?

(Hope that's understanable...

 

[T. Valko]

Yeah, that sounds like that was the "problem". Personally, I find all those
"warnings" to be annoying so I have them all turned off.