dates and days of the week

wilcox96

Board Regular
Joined
Jan 5, 2005
Messages
169
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

MycroftII

New Member
Joined
Sep 30, 2004
Messages
29
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...
 

Forum statistics

Threads
1,147,622
Messages
5,742,197
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top