Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Function
Hello Friends
I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452. I've got the number of employes I used on that date next to the date in col B. What I want is to have 1-52 in Col A at the top of the sheet and the number of employees I used in that week of the year across from the corresponding number in Col B. I've tried =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452 )) but keep getting a #VALUE! error. Thanks for any and all help. I appreciate your being there! Sam |
#2
|
|||
|
|||
Sam,
Unfortunately, WEEKNUM will not return an array, don't know why, it is just implemented that way. This should work for you though =SUMPRODUCT((1+INT(($A$87:$A$452-(DATE(YEAR($A$87:$A$452),1,2)-WEEKDAY(DATE( YEAR($A$87:$A$452),1,1))))/7)=A1)*(B$87:B$452)) -- HTH RP (remove nothere from the email address if mailing direct) "Metolius Dad" wrote in message ... Hello Friends I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452. I've got the number of employes I used on that date next to the date in col B. What I want is to have 1-52 in Col A at the top of the sheet and the number of employees I used in that week of the year across from the corresponding number in Col B. I've tried =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452 )) but keep getting a #VALUE! error. Thanks for any and all help. I appreciate your being there! Sam |
#3
|
|||
|
|||
One way would be to put 01/01/2005 in A1
Put =A1+7 in B1 and drag across for your 52 weeks Then in A2 =SUMPRODUCT(--($A$87:$A$452=A1),--($A$87:$A$452<B1),--($B$87:$B$452)) Copy across through B2 etc. Change the value in A1 to create the correct week for what you regard as being week 1 -- Regards Roger Govier "Metolius Dad" wrote in message ... Hello Friends I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452. I've got the number of employes I used on that date next to the date in col B. What I want is to have 1-52 in Col A at the top of the sheet and the number of employees I used in that week of the year across from the corresponding number in Col B. I've tried =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452 )) but keep getting a #VALUE! error. Thanks for any and all help. I appreciate your being there! Sam |
#4
|
|||
|
|||
Sorry, I have noticed since posting, you asked for the weeks to be in
A1:A52, not across the page Put 01/01/2005 in A1 In A2 =A1+7 Copy down through A3:A52 Amend formula and place in B1 =SUMPRODUCT(--($A$87:$A$452=A1),--($A$87:$A$452<A2),--($B$87:$B$452)) Copy down through B2:B52 -- Regards Roger Govier "Roger Govier" wrote in message ... One way would be to put 01/01/2005 in A1 Put =A1+7 in B1 and drag across for your 52 weeks Then in A2 =SUMPRODUCT(--($A$87:$A$452=A1),--($A$87:$A$452<B1),--($B$87:$B$452)) Copy across through B2 etc. Change the value in A1 to create the correct week for what you regard as being week 1 -- Regards Roger Govier "Metolius Dad" wrote in message ... Hello Friends I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452. I've got the number of employes I used on that date next to the date in col B. What I want is to have 1-52 in Col A at the top of the sheet and the number of employees I used in that week of the year across from the corresponding number in Col B. I've tried =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452 )) but keep getting a #VALUE! error. Thanks for any and all help. I appreciate your being there! Sam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date function | Excel Worksheet Functions | |||
Excel date function | Excel Worksheet Functions | |||
Is there a function to show future date | Excel Worksheet Functions | |||
Date Function | Excel Worksheet Functions | |||
Date Overdue function, Macro, or VBS | Excel Discussion (Misc queries) |