# dates and days of the week

#### wilcox96

##### Board Regular
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### MycroftII

##### New Member
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...

Replies
15
Views
126
Replies
5
Views
129
Replies
1
Views
238
Replies
3
Views
144
Replies
3
Views
315

1,181,614
Messages
5,930,937
Members
436,767
Latest member
Langaws

### 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.

### Which adblocker are you using?

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

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