[URGENT] Linking Dates to a Data Worksheet

[Guest]

I have a control sheet with hire dates and values for these hires. I have another worksheet with week ending dates on it. I need to find a way to link these values into the weeks ending, based on the start date. I know how to use the sum if formula but I dont know how to do it with dates. Since 6/1 and 6/6 will go in the same cell. I need to have my system automated so this is really important. Thanks.

 

[Myrna Larson]

Am I correct that you want to sum all the values for the new hires, by week?

I'll assume the work week is Mon-Fri, and your weeks start on either Sunday or
Monday (it won't matter which as long as you don't hire anybody on Sunday).
The WEEKNUM function will calculate a week-number for each hire-date. You
would need to put that in an additional column.

I'll assume your control sheet has the name is in column A, hiredate is in
column B, values in column C. You'll put the week number in column D via this
formula: =WEEKNUM(B2).

Then, for your SUMIF formulas, let's say you have a week-ending date in K1.

=SUMIF($D$2:$D$100,WEEKNUM($K$1),$C$2:$C$100)

With this approach, you don't even need the list of week-ending dates on the
2nd sheet.

If this won't work for you, you could modify the table on the 2nd sheet to
include the START date of the week. Assuming you now have ending dates in
column A, You could do that by inserting a new column A, then put the formula
=B2-6 in A2 and copy it down. That will give you week start in A and week end
in B.

On the first sheet, the formula to get the week number becomes

=MATCH(B2,Sheet2!$A$2:$A$53)

In the SUMIF formulas, you replace WEEKNUM(K1) with a MATCH formula:

=SUMIF($D$2:$D$100,MATCH($K$1,Sheet2!$A$2:$A$53),$C$2:$C$53)

I have a control sheet with hire dates and values for these hires. I have

another worksheet with week ending dates on it. I need to find a way to link
these values into the weeks ending, based on the start date. I know how to use
the sum if formula but I dont know how to do it with dates. Since 6/1 and 6/6
will go in the same cell. I need to have my system automated so this is really
important. Thanks.

 

[dave]

try this out - formula in f28 column -
{=SUM(($C$28:$C$36<=E28)*($C$28:$C$36>E27)*$D$28:$D$36)}
Brackets means array formula so hit ctrl shift enter
instead of just enter after typing in formula, and then
copy down. Since formula refers to previous week ending
date to determine starting point for formula, F27 should
be typed as N/A.
week ending dates
Col/row #s C D E F
27 05/30/04 10 05/28/04 N/A
28 06/01/04 20 06/04/04 100
29 06/04/04 80 06/11/04 70
30 06/07/04 5 06/18/04 644
31 06/10/04 65 06/25/04 31
32 06/13/04 604 07/02/04 0
33 06/16/04 40 07/09/04 0
34 06/19/04 14 07/16/04 0
35 06/22/04 1 07/23/04 0
36 06/25/04 16 07/30/04 0

hth,
Dave

-----Original Message-----
I have a control sheet with hire dates and values for

these hires. I have another worksheet with week ending
dates on it. I need to find a way to link these values
into the weeks ending, based on the start date. I know how
to use the sum if formula but I dont know how to do it
with dates. Since 6/1 and 6/6 will go in the same cell. I
need to have my system automated so this is really
important. Thanks.

 

[Debra Dalgleish]

You could use a SumProduct formula. For example, with week ending dates
starting in cell A2, hire dates in column A on sheet HireData, and
values in column G of HireData, enter the following formula in row 2,
and copy down:


=SUMPRODUCT((HireData!$A$2:$A$500>=A2-6)*(HireData!$A$2:$A$500<=A2)*(HireData!$G$2:$G$500))

 

[Guest]

Thank you so much for your help with this. That is perfect. Now there is one more little catch. I also need to have it sum if according to the person who hired them. Meaning I need to sort the new hires by both start date and the person who hired them. How do i sort by both

 

[Myrna Larson]

For two conditions you need SUMPRODUCT or an array formula, and you would'n't
need the extra column for the weeknumber. You could calculate that "on the
fly". I'll assume you've assigned names to the data columns,
Emp,HireDate,Hirer,Values.

=SUMPRODUCT((WEEKNUM(HireDate=K1)*(Hirer=K2)*Values))

Thank you so much for your help with this. That is perfect. Now there is one

more little catch. I also need to have it sum if according to the person who
hired them. Meaning I need to sort the new hires by both start date and the
person who hired them. How do i sort by both