Can Date functions be used to convert Daily Dataset to Weekly?

Dear all,

I have a dataset in the following format containing 12 years of daily data for each of the 40 district areas.  i.e. 1/1/2002 will contain 40 records for each of the 40 districts.  The example data below is for two districts only for two dates thought more of the dataset would make the question too long.

Date          District  Loss  Hectare  Count
1/01/2002   1           10    10          10
1/02/2002   2           20    20          20
2/02/2002   1           11    11          11
2/02/2002   2           21    21          21

Is it possible to use the Date function to aggregate the data to weekly?

See below output example, record 1 aggregates all values from 1/01/2002 to 7/01/2002 for the selected district area (value of district field).  The next data record aggregates all values from 8/01/2002 to 14/01/2002 for the selected district area (value of district field) and so on.

Is there a way to accompish this I haven't been able to find a solution.  

I've tried pivot tables in MS Excel but having issues with the district field, haven't been able to get the district into a column like below.  The feature to group days is great.

Date            District  TotalLoss  TotalHectare  TotalCount
1/01/2002    1           50            100                 150
14/01/2002  1           50            100                 150

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can group by Format([Date], "yyyyww", 2, 2)

Note however, that VBA has a bug for the ISO week numbering for week 53.

AndyC1000Author Commented:
Thanks - what do you mean by ' bug for the ISO week numbering for week 53'?
Gustav BrockCIOCommented:
Since version 1.0 and still in .Net, some weeks of no. 1 are returned as week 53.

This function does it right:
Public Function ISO_WeekNumber( _
  ByVal datDate As Date) _
  As Byte

' Calculates and returns week number for date datDate according to the ISO 8601:1988 standard.
' 1998-2000, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  Dim bytWeek                   As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = WeekDay(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(Year(datDate), 12, 31)
    If WeekDay(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
      ' Correct for Access97/2000 bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If
  ISO_WeekNumber = bytWeek

End Function

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
Whicb version of Excel are you using?

With xl2007 and later there is SUMIFS function where you can specify multiple criteria for a summary total.

The criteria would be for example "District = 1", "Date => 01/01/2002", "Date <08/01/2002".

Alternatively you may be able to use Pivot Table with a helper column for week ending date.

Assuming date in column A, you can formulate the week ending date in a separate column:


Copied down to accomodate all data rows.

This will round up the date to the next factor of 7, ie the Saturday. Dates are captured as a serial number as number of days since 01 Jan 1900 which was a Sunday with that day being serial number 1; today (4 Sept 2013) is serial number 41521. With day 7 being a Saturday, each and every Saturday after becomes a multiple of 7.

You can then use the Week End date as a grouping in the Pivot Table.

Rob H
Dale FyeCommented:
You could also do a grouping on a computed field, something like:

Select DateAdd("d", -Weekday([DateField]), [DateField]) as WeekStart,
          District, Sum([Loss]) as WeekLoss, Sum([Hectare]) as WeekHectare,
          Sum([Count]) as WeekCount
FROM yourTable
GROUP BY DateAdd("d", -Weekday([DateField]), [DateField]) as WeekStart, District

Depending on what day of the week you want to start your week, you might need to tweak the DateAdd function by adding a numeric value (N) into that function.  Something like:

DateAdd("d", [N]-Weekday([DateField]), [DateField])

I would start out with a simple query that looks something like:

SELECT [DateField], DateAdd("d", [N]-Weekday([DateField]), [DateField]) as WeekStart
FROM yourTable
WHERE [District] = 1

When prompted for [N], enter zero (0).  Look to see whether the values of [WeekStart] correspond to the start day of your week (generally either Sun or Mon).  If not, change the value of N until the Week start values correspond to what you are looking for.  Once you have that right, replace the parameter [N] with the actual number you were entering

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndyC1000Author Commented:
Thanks all.

I've decided to go ahead with Fyed's solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.