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." <Jonathan [email protected]> wrote in message
news:[email protected]...
> 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.

[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" wrote:

> Try
>
> =SUMPRODUCT(--(H5:H30<=H1),--(I5:I30>H1))
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jonathan B." <Jonathan [email protected]> wrote in message
> news:[email protected]...
> > 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]

Of course I will <g>

--
HTH

Bob Phillips

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

"Jonathan B." <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > Try
> >
> > =SUMPRODUCT(--(H5:H30<=H1),--(I5:I30>H1))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Jonathan B." <Jonathan [email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >