CODEWITHSUNDEEP
excelms-accessvba
Abhay Kumar
Abhay Kumar

Reputation: 1618

Accumulate Daily data on weekly basis

I am new to excel formulas and macros. My sheet is show below.

id   Date      name   hours code    workPackage
1   3/28/2012   Anit    8   N1      1212
2   3/29/2012   Anit    8   N2      1212
3   3/30/2012   Anit    8   N2      1214
4   3/31/2012   Anit    8   N4      1212
5   4/1/2012    Anit    5   N1      1212
6   4/1/2012    Anit    3   N2      1212
7   4/3/2012    Anit    8   N2      1222
8   4/4/2012    Anit    8   N2      1212
9   4/5/2012    Anit    8   A1      1212
10  4/6/2012    Anit    8   A1      1212

I want to accumulate this daily data into a weekly (On fridays) based on Code and workPackage. For a particular Code and workpackage add the Hours for that week. The final output data should be something like this. 

1   3/30/2012   Anit    8   N1      1212
2   3/30/2012   Anit    8   N2      1212
3   3/30/2012   Anit    8   N2      1214
4   4/6/2012    Anit    16  A1      1212
5   4/6/2012    Anit    5   N1      1212
6   4/6/2012    Anit    19  N2      1212
7   4/6/2012    Anit    8   N4      1212

If a query could be written by importing this data in a access DB or any other DB for the that, it would also be great.

Upvotes: 0

Views: 981

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

You can use ADO in Excel. The query would be:

Select Code, WorkPackage, Max([date]), First([Name]), Sum([Hours])
From [Sheet1$]
Group By Code, WorkPackage

Ref: http://support.microsoft.com/kb/257819

Edit re Comment

SELECT t.code, t.workPackage, Format([Date],"yyyy-ww") AS WeekNo, 
       Min(t.Date) AS MinOfDate, Max(t.Date) AS MaxOfDate, 
       Min(t.name) AS MinOfname, Sum(t.hours) AS SumOfhours
FROM [Sheet1$] t
GROUP BY t.code, t.workPackage, Format([Date],"yyyy-ww");

Upvotes: 2

Related Questions