WorkWeek

[Richard]

In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum
% in D. Can this be done?

 

[Bob Phillips]

Do you mean all dates in column B being in the same week?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Your question is not entirely clear. Are you asking to see if the workweek
for the date in E1 matches ANY workweek for ANY date in Column B and, if it
does, return the sum of the percentages in Column D? If not, please clarify.
If so, which percentages... all of them or just the ones with matching
workweeks?

Rick

 

[Richard]

just the % for each week. Match each workweek and return the percentage for
each one: Example:
Column B will have such as 6/2/08, 6/3/08/,6/4/08,6/5/08,6/6/08, Column D
will have the percentages for each day. I then need in column E to give me
the percentage for the entire week. And so on for each following week. Thanks
in advance for your help.

 

[Richard]

just the % for each week. Match each workweek and return the percentage for
each one: Example:
Column B will have such as 6/2/08, 6/3/08/,6/4/08,6/5/08,6/6/08, Column D
will have the percentages for each day. I then need in column E to give me
the percentage for the entire week. And so on for each following week. Thanks
in advance for your help.

 

[Shane Devenshire]

Hi Richard,

Suppose your workweek is defined as Mon-Fri. With dates in B2:B102 and data
in D2102.

To get the overall % of column D that are for any weekdays in column B use
the formula:

=SUM((WEEKDAY(B2:B102,2)<6)*D2102)/SUM(D2102)

This formula is entered as an array - Press Shift Ctrl Enter instead of
enter or use:

=SUMPRODUCT((WEEKDAY(B2:B102,2)<6)*D2102)/SUM(D2102)

To get the % for a specific week say Jan 7, 2008 to Jan 11, 2008:

=SUM((B2:B102>=DATE(2008,1,7))*(B2:B102<=DATE(2008,1,11))*D2102)/SUM(D2102)

Again entered as an array or using SUMPRODUCT without array entry.

Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver and
help search for life beyond earth.

 

[Richard]

Date Actual % Ran Weekly % Production
6/2/08 100.00%
6/3/08 100.00%
6/4/08 50.00%
6/5/08 50.00%
6/6/08 50.00%

Thanks for your help but I still can not get it. On the Weekly % Production
I need for it to show 70% by this example at the end of the Week or whatever
the results may be. Like 6/2/98 would be 100%, 6/3/08 would be100%, 6/4/08
would be 83%, 6/5/08 would be 75%, 6/6/08 would be 70% etc... Thanks in
Advance!!!

 

[Max]

A tinker for you to try out over there ..

My assumptions: Real dates in B2 down, % in D2 down, desired "Weekly %
production" in E2 down, source data in cols B and D expected within row 2 to
100 (say)

In E2:
=IF(B2="","",SUMPRODUCT(--(F$2:F$100=weeknum(B2,2)),D$2$100))

In F2:
=IF(B2="","",weeknum(B2,2))
Select E2:F2, copy down to F100. Col E returns the desired results.

P/s: "Weeknum" requires the Analysis ToolPak to be loaded