Number of dates falling in a date range
 

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?

Number of dates falling in a date range
 

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)

--
Biff
Microsoft Excel MVP


"Cralis" wrote in message
...
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?

Number of dates falling in a date range
 

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?

Number of dates falling in a date range
 

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


--
Biff
Microsoft Excel MVP


"Cralis" wrote in message
...
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?

Number of dates falling in a date range
 

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

Number of dates falling in a date range
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Cralis" wrote in message
...
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

Number of dates falling in a date range
 

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... :)

Number of dates falling in a date range
 

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

--
Biff
Microsoft Excel MVP


"Cralis" wrote in message
...
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... :)