dates and days of the week

[wilcox96]

here's the scenario....

i want to type in dates (start date and end date). from there, i've got other cells in the row that are under each corresponding day of the week. i can then add numbers in the day of the week cells that correspond to the dates listed..and ultimately i would like to total the numbers listed in each day calculated by the date range. (example would be... start date: 1/10/05. end date: 1/23/05. in the day of the week fields i might have 2 on mon, 2 on tues, 2 on wed, 2 on thurs, and 2 on fri. this is a total of 10 for the week, but since the date range spans 2 weeks, the total would actually need to be 20. the trick is to get it to recognize the date range, days of the week and what days to count within the designated date range.
(another example might be, start date 1/13/05, end date 1/19/05. put 2 on mon, tues, wed, thurs, fri. since the dates provided are thurs-wed, it should calculate there are a total of 10 .... 2 on each day specified....)??

anyway... i think there are at least 2 problems to be solved. recognizing date ranges...and totaling the numbers in the "days of the week" columns based on those dates. (this final total would be in a separate "totals" cell. any thoughts? hey..while we are at it...how about a pop up monthly calendar that when you clicked a day/date, it would show up on the cell?

 

[MycroftII]

Try:

=(WEEKNUM(Enddate)-WEEKNUM(Startdate)+1)*SUM(WeeklyHours)

where Enddate is the reference to the cell of the end date, (startdate dito) and WeeklyHours refers to the array containing all hours of between this range.

Regarding the second idea: Take a look at comment #6 in http://www.dicks-blog.com/archives/2004/06/23/ugly-formulas/ - though not exaclty what you want it might help a lot...