I've a series of dates i want grouped 2 use for averaging totals

M

Moo

I have a series of dates, which i want to group themselves into say weeks, so
i can use the weeks to calculate weekly averages.

I've been cirlcling round trying to find this function,
Either I'm having a blind moment or I need your help...eitherway i need your
help..

Cheers
 
D

Duane Hookom

You can convert a date value to a week value by using
DatePart("ww",[SeriesOfDates])
 
M

Marshall Barton

Moo said:
I have a series of dates, which i want to group themselves into say weeks, so
i can use the weeks to calculate weekly averages.

I've been cirlcling round trying to find this function,
Either I'm having a blind moment or I need your help...eitherway i need your
help..
Click to expand...


Grouping by month,quarter or year is simple, just group on
an expression like:
Format(datefield, "yyyymm")
Format(datefield, "yyyyq")
DatePart("yyyy",datefield)

But, weeks are a tricky think because you need to know what
day a week starts on and you might even need to know when
the first week in a year starts. If you have that in mind,
then you could use:
Format(datefield,"yyyyww", firstdayofweek, firstweekofyear)
check VBA Help for details.
 
J

John Spencer

AND it gets even trickier if you are looking at individual dates in the week
and the week is split between years. Depending on your settings the first
few days of the week could fall in one year as the last week of the year and
the last days of the week could fall in the following year as week 1.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top