|
Sum of weekly data
Hi,
Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
What happens if a month consists of more than four weeks? E.g.
1st week of July 2008 is 1-July to 6-July 2nd week of July 2008 is 7-July to 13-July .... 5th week of July 2008 is 28-July to 31-July Regards, Stefi €˛Praveen Potturi€¯ ezt Ć*rta: Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
How do you define a week? Is Week1 the first seven days in a month, the
days up to the first Sat/Sun? Or some other rule? -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
Hi Stefi,
I need data of all weeks whether its 4 weeks or 5 weeks. Awaiting for response. -- Thanks, Praveen Potturi "Stefi" wrote: What happens if a month consists of more than four weeks? E.g. 1st week of July 2008 is 1-July to 6-July 2nd week of July 2008 is 7-July to 13-July ... 5th week of July 2008 is 28-July to 31-July Regards, Stefi €˛Praveen Potturi€¯ ezt Ć*rta: Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
hi Bob,
Week means only weekdays i.e Monday to Friday. Sat and Sun shouldnot be considered. -- Thanks, Praveen Potturi "Bob Phillips" wrote: How do you define a week? Is Week1 the first seven days in a month, the days up to the first Sat/Sun? Or some other rule? -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
Hi
I would insert a new row above your data and in B1 enter =WEEKNUM(B2) Copy across through C1:AF1 In your "new" row 14, in A14 enter Week in B14:F14 enter 27,28,29,30,31 In B15 enter =SUMPRODUCT(($B$1:$AF$1=B$14)*(WEEKDAY($B$2:$AF$2, 2)<6)*$B3:$AF3) Format cell B15, FormatCellsNumberCustom [hh]:mm and copy across through C15:F15 This will give your the total of hours for that week number for weekdays only for hours spent in Break Copy B15:F15 down through B16:B20 to give the totals for the other Task types. Each month you will need to change the values in cells B14:F14 to reflect the week numbers belonging to that particular month. Sometimes there will be 4 weeks and sometimes 5. Just leave cell F14 blank when it is a 4 week month. -- Regards Roger Govier "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
Yes, but for instance, is Week 1 in this month July, the 1st to the 7th, or
1st to 4th because the 7th day is Monday and is therefore week2. You must to have VERY explicit rules. -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... hi Bob, Week means only weekdays i.e Monday to Friday. Sat and Sun shouldnot be considered. -- Thanks, Praveen Potturi "Bob Phillips" wrote: How do you define a week? Is Week1 the first seven days in a month, the days up to the first Sat/Sun? Or some other rule? -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
Week1 for July should be from 1st to 4th, week2 should be from 7th to 11th,
week3 should be from 14th to 18th and so on and week5 will be 28th to 31st. -- Thanks, Praveen Potturi "Bob Phillips" wrote: Yes, but for instance, is Week 1 in this month July, the 1st to the 7th, or 1st to 4th because the 7th day is Monday and is therefore week2. You must to have VERY explicit rules. -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... hi Bob, Week means only weekdays i.e Monday to Friday. Sat and Sun shouldnot be considered. -- Thanks, Praveen Potturi "Bob Phillips" wrote: How do you define a week? Is Week1 the first seven days in a month, the days up to the first Sat/Sun? Or some other rule? -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
Hi Roger,
Weeknum function is not working for me. Plz help -- Thanks, Praveen Potturi "Roger Govier" wrote: Hi I would insert a new row above your data and in B1 enter =WEEKNUM(B2) Copy across through C1:AF1 In your "new" row 14, in A14 enter Week in B14:F14 enter 27,28,29,30,31 In B15 enter =SUMPRODUCT(($B$1:$AF$1=B$14)*(WEEKDAY($B$2:$AF$2, 2)<6)*$B3:$AF3) Format cell B15, FormatCellsNumberCustom [hh]:mm and copy across through C15:F15 This will give your the total of hours for that week number for weekdays only for hours spent in Break Copy B15:F15 down through B16:B20 to give the totals for the other Task types. Each month you will need to change the values in cells B14:F14 to reflect the week numbers belonging to that particular month. Sometimes there will be 4 weeks and sometimes 5. Just leave cell F14 blank when it is a 4 week month. -- Regards Roger Govier "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
=SUMPRODUCT((MONTH($B$1:$AF$1)=7)*(WEEKDAY($B$1:$A F$1,2)<6)*
((DATE(2008,7,-DAY($B$1))+(COLUMN()-1)*7)=$B$1:$AF$1)* ($B$1:$AF$1<DATE(2008,7,-DAY($B$1))+(COLUMN()-1)*7)* ($B$2:$AF$8))- N(SUM($A12:A12)) -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... Week1 for July should be from 1st to 4th, week2 should be from 7th to 11th, week3 should be from 14th to 18th and so on and week5 will be 28th to 31st. -- Thanks, Praveen Potturi "Bob Phillips" wrote: Yes, but for instance, is Week 1 in this month July, the 1st to the 7th, or 1st to 4th because the 7th day is Monday and is therefore week2. You must to have VERY explicit rules. -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... hi Bob, Week means only weekdays i.e Monday to Friday. Sat and Sun shouldnot be considered. -- Thanks, Praveen Potturi "Bob Phillips" wrote: How do you define a week? Is Week1 the first seven days in a month, the days up to the first Sat/Sun? Or some other rule? -- __________________________________ HTH Bob "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. |
Sum of weekly data
Did you use Tools|Addins and make sure that the analysis toolpak was checked.
Praveen Potturi wrote: Hi Roger, Weeknum function is not working for me. Plz help -- Thanks, Praveen Potturi "Roger Govier" wrote: Hi I would insert a new row above your data and in B1 enter =WEEKNUM(B2) Copy across through C1:AF1 In your "new" row 14, in A14 enter Week in B14:F14 enter 27,28,29,30,31 In B15 enter =SUMPRODUCT(($B$1:$AF$1=B$14)*(WEEKDAY($B$2:$AF$2, 2)<6)*$B3:$AF3) Format cell B15, FormatCellsNumberCustom [hh]:mm and copy across through C15:F15 This will give your the total of hours for that week number for weekdays only for hours spent in Break Copy B15:F15 down through B16:B20 to give the totals for the other Task types. Each month you will need to change the values in cells B14:F14 to reflect the week numbers belonging to that particular month. Sometimes there will be 4 weeks and sometimes 5. Just leave cell F14 blank when it is a 4 week month. -- Regards Roger Govier "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. -- Dave Peterson |
Sum of weekly data
Thanks Dave.
Totally forgot to mention that -- Regards Roger Govier "Dave Peterson" wrote in message ... Did you use Tools|Addins and make sure that the analysis toolpak was checked. Praveen Potturi wrote: Hi Roger, Weeknum function is not working for me. Plz help -- Thanks, Praveen Potturi "Roger Govier" wrote: Hi I would insert a new row above your data and in B1 enter =WEEKNUM(B2) Copy across through C1:AF1 In your "new" row 14, in A14 enter Week in B14:F14 enter 27,28,29,30,31 In B15 enter =SUMPRODUCT(($B$1:$AF$1=B$14)*(WEEKDAY($B$2:$AF$2, 2)<6)*$B3:$AF3) Format cell B15, FormatCellsNumberCustom [hh]:mm and copy across through C15:F15 This will give your the total of hours for that week number for weekdays only for hours spent in Break Copy B15:F15 down through B16:B20 to give the totals for the other Task types. Each month you will need to change the values in cells B14:F14 to reflect the week numbers belonging to that particular month. Sometimes there will be 4 weeks and sometimes 5. Just leave cell F14 blank when it is a 4 week month. -- Regards Roger Govier "Praveen Potturi" wrote in message ... Hi, Can anyone help in doing sum of daily data with respect to weekdays. I want to get the sum of everydays data as week1, week2..etc. for that month Example:- A B C D E 1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul 2 Break 0:00:00 0:00:00 0:00:00 0:00:00 3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00 4 Training 0:00:00 0:00:00 0:00:00 0:00:00 5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00 6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00 7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00 8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00 9 Total 0:00:00 0:00:00 0:00:00 0:00:00 10 11 12 13 Week1 Week2 Week3 Week4 14 Break 15 Meeting 16 Training In the above example I want a formula in B14, C14, D14, E14 which can do sum of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't have to map it everytime manually. I want excel to recongnize the data according to the dates in B1, C1, D1. So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of B2+C2+D2 and result should show in B14. -- Dave Peterson |
| All times are GMT +1. The time now is 08:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com