Counting dates in a spreadsheet....

[Jonathan B.]

I have a spreadsheet that is supposed to count the number of projects that
are active on the date a report is presented.

(ex. 06/05/06 report.
Project 1 start date 12/14/05 end date 02/04/07
Project 2 start date 06/07/06 end date 09/05/06
Project 3 start date 06/05/06 end date 11/12/06
Project 4 start date 06/09/06 end date 08/31/06

= 2 active projects (Project 2 and 3) on the date the report was run.)

I have tried using =COUNTIF(H5:H30,"<H1")+COUNTIF(I5:I30,">=H1")
with H1 being the date of the report and H5:h30 being the start date and
I5:I30 being the end date of the projects. I would assume the formula would
compare the date against the H column and give it a positive number then go
down the I column and give another positive number then subtract H from I and
I would have the total number of active projects during the date of the
report, but it comes back 0 if I format the cell to General or 1/1/1900 if I
format using a date.

I hope this makes sense to the experts. Any advice would be appreciated.

 

[Bob Phillips]

Try

=SUMPRODUCT(--(H5:H30<=H1),--(I5:I30>H1))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jonathan B.]

All hail the mighty Bob!

Thank you! I was bangin my head for hours yesterday. I plug in that formula
today and sure enough. I got another number other than 0 and 1/1/1900 so I
had to verify the number and sure enough it worked.

I appreciate it.

Now I can look like a genius here at work since the project was assigned to
me. (you'll cover for me won't ya?)

Thanks again,

Jonathan

(I am a Excel novice. I have been learning as I go. I appreciate the lesson.)

 

[Bob Phillips]

Of course I will <g>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)