• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

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

3 Solutions
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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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
AndyC1000Author Commented:
Thanks all.

I've decided to go ahead with Fyed's solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now