How can I have excel sort through a data string of dates?
 

I am building a database that allows users to enter their weekly activities.
Each user will have their own page and will be able to enter the date they
worked into the form as well as the activity they participated in. I have
embedded formulas that caluclate how many "points" each of these activity
sessions is worth.
My problem is that I am trying to build a formula that searches through the
entered dates, extracts the associated points out of the database for each
quarter, and totals the points. For instance, I would like the function to
look through 100 entries, determine which entries occurred between July 1st
and September 30th and total all the points that occurred in that time
period. It doesn't seem that difficult, but I am really struggling to make
Excel sift through dates.
Help???

Thanks!

Hi!

Dates in A1:100
Points in B1:B100

=SUMPRODUCT(--(A1:A100=DATE(2005,7,1)),--(A1:A100<=DATE(2005,9,30)),B1:B100)

Better:

C1 = 7/1/2005
D1 = 9/30/2005

=SUMPRODUCT(--(A1:A100=C1),--(A1:A100<=D1),B1:B100)

Biff

"LionNav" wrote in message
...
I am building a database that allows users to enter their weekly
activities.
Each user will have their own page and will be able to enter the date they
worked into the form as well as the activity they participated in. I
have
embedded formulas that caluclate how many "points" each of these activity
sessions is worth.
My problem is that I am trying to build a formula that searches through
the
entered dates, extracts the associated points out of the database for each
quarter, and totals the points. For instance, I would like the function
to
look through 100 entries, determine which entries occurred between July
1st
and September 30th and total all the points that occurred in that time
period. It doesn't seem that difficult, but I am really struggling to
make
Excel sift through dates.
Help???

Thanks!

Biff,

Thanks! That was perfect!

By chance, do you know of a way to create a drop down menu calendar with
Excel? I am trying to employ something similar to the calendar that the
various travel websites use.

Thanks again - you are a lifesaver.



"Biff" wrote:

Hi!

Dates in A1:100
Points in B1:B100

=SUMPRODUCT(--(A1:A100=DATE(2005,7,1)),--(A1:A100<=DATE(2005,9,30)),B1:B100)

Better:

C1 = 7/1/2005
D1 = 9/30/2005

=SUMPRODUCT(--(A1:A100=C1),--(A1:A100<=D1),B1:B100)

Biff

"LionNav" wrote in message
...
I am building a database that allows users to enter their weekly
activities.
Each user will have their own page and will be able to enter the date they
worked into the form as well as the activity they participated in. I
have
embedded formulas that caluclate how many "points" each of these activity
sessions is worth.
My problem is that I am trying to build a formula that searches through
the
entered dates, extracts the associated points out of the database for each
quarter, and totals the points. For instance, I would like the function
to
look through 100 entries, determine which entries occurred between July
1st
and September 30th and total all the points that occurred in that time
period. It doesn't seem that difficult, but I am really struggling to
make
Excel sift through dates.
Help???

Thanks!

Hi!

By chance, do you know of a way to create a drop down menu calendar with
Excel?

Sorry, I don't. But, that's a frequently asked question here so if you were
to do a search on it I'm sure you'll what you need.

Biff

"LionNav" wrote in message
...
Biff,

Thanks! That was perfect!

By chance, do you know of a way to create a drop down menu calendar with
Excel? I am trying to employ something similar to the calendar that the
various travel websites use.

Thanks again - you are a lifesaver.



"Biff" wrote:

Hi!

Dates in A1:100
Points in B1:B100

=SUMPRODUCT(--(A1:A100=DATE(2005,7,1)),--(A1:A100<=DATE(2005,9,30)),B1:B100)

Better:

C1 = 7/1/2005
D1 = 9/30/2005

=SUMPRODUCT(--(A1:A100=C1),--(A1:A100<=D1),B1:B100)

Biff

"LionNav" wrote in message
...
I am building a database that allows users to enter their weekly
activities.
Each user will have their own page and will be able to enter the date
they
worked into the form as well as the activity they participated in. I
have
embedded formulas that caluclate how many "points" each of these
activity
sessions is worth.
My problem is that I am trying to build a formula that searches through
the
entered dates, extracts the associated points out of the database for
each
quarter, and totals the points. For instance, I would like the
function
to
look through 100 entries, determine which entries occurred between July
1st
and September 30th and total all the points that occurred in that time
period. It doesn't seem that difficult, but I am really struggling to
make
Excel sift through dates.
Help???

Thanks!