Hi Greg, I think I understand what your saying.
I think I did a bad job explaining my situation. I was in a hurry.
I use Excel 2003 and Query to query a database of operators, their time and
workorders with the date the work was done.
The query is determined by selecting a persons name from a drop down list
and comparing that name to the names in the data base. The query then pulls
all the records by that name and orders them by the date.
I then have a sum that adds ALL the hours in the hours column and gives me a
running total for the year. What I want next is to sum only the hours that
pertain to a given week.
I can make this happen on an individual basis but if I then select a
different name, the data all changes and the conditional sum returns
erroneous information.
I've tried WEEKNUM and SUMIF and other logical functions but I know I'm
missing something simple. I'm new at this programming stuff and learning on
the fly with no training. I only recently discovered the wonderful world of
VLOOKUP and HLOOKUP.
In short I'd like to be able to examine a column of dates, determine what
week each date is in and then sum the hours for that week only.
Thanks for the feedback.
"Greg Glynn" wrote in message
oups.com...
Hi Tom ...Yes, I'm with you so far.

Do you want the spreadsheet to record the total for a week, or is just
an ad hoc enquiry that you might want to occasionally perform? (ie,
do you need a cell for the result?)

If you just want to perform an ad hoc calc, then you might try using
the Worksheet_BeforeDoubleClick or Worksheet_BeforeRightClick event to
trigger a macro to:

(1) calculate the cell address of the doubleclick and then test to see
what day of the week it corresponds to
(2) find the next Friday my moving right and performing a weekday()
test
(3) one you find Friday, run a little loop that looks back 5 days (it
might have to look back at every 5th column) and do a quick addition
(4) pop up a msgbox() with the result

If you want to record the weekly total in a column, you need to do the
same, and add a column after the friday of each week (however, you
might run out of columns unless your running Excel 2007).

With me so far?