This would work as a pivot table using an active time series... Create a
pivot table off of the data. Add the dates to the Left column and the amounts
to the main body. Right click on the dates and group them by month, quarter,
year or whatever. You will still have some difficulties with weeks that
stradle over months so if that is a problem you will have to expand your
weekly values to daily values. I would need to know a little more about
stradling the data over months based on a 7 day week or a 5 day work week.

"Dan Thompson" wrote:

Hi Everyone,
I am looking for a Excel Macro to convert Weekly Data into Monthly data.
I am able to do this by using the excel formulas. I have two data sets one
is weekly.
so for example ..

2-Jan-99 119.42
9-Jan-99 121.09
16-Jan-99 121.49
23-Jan-99 121.44
30-Jan-99 121.33
6-Feb-99 120.33
13-Feb-99 120.5
20-Feb-99 122.36
27-Feb-99 121.81

is there a macro that I could use to convert this data to monthly values by
averaging
all weekly values in jan and feb to give me 1 monthly value for Jan and 1
for Feb and so on for the entire data set. If this is posible could someone
please help me out with some Ideas. Also I want the finished monthly values
to apear in another column or sheet.

Any thoughts ??

Dan.

Thanks Jim I appreciate the help, unfortunatly that is probably not going to
work for me, for a few reasons. 1) I don't know anything about pivot tables.
2) From your suggestions it sounds like I would have to repate that procedure
every time I need to convert weekly to monthly data, and ufortunatly I have
far far to much data litterly 1000's of data sets to convert from weekly to
monthly and although your suggestions were good it seems a little to laborous
to do it with pivot tables.

What I need is a macro that will write formuals into cells after doing some
checks on the weekly dates the formulas that the VBA macros would automaticly
write into the cells would be somthing like this


=IF(ISNUMBER(A5),AVERAGE(A1:A5),"") <---- Average of 5 weekly values in Jan
to

Give me 1 monthly Value in the cell

That contains this formula

But I Need VBA to Determin Where to start and stop with the "Average"
Formula otherwise I might be averaging Jan and part of Feb into one monthly
value for Jan.

Anyhow not sure if anyone can help but I appreciate your input anyway Jim.



"Jim Thomlinson" wrote:

This would work as a pivot table using an active time series... Create a
pivot table off of the data. Add the dates to the Left column and the amounts
to the main body. Right click on the dates and group them by month, quarter,
year or whatever. You will still have some difficulties with weeks that
stradle over months so if that is a problem you will have to expand your
weekly values to daily values. I would need to know a little more about
stradling the data over months based on a 7 day week or a 5 day work week.

"Dan Thompson" wrote:

Hi Everyone,
I am looking for a Excel Macro to convert Weekly Data into Monthly data.
I am able to do this by using the excel formulas. I have two data sets one
is weekly.
so for example ..

2-Jan-99 119.42
9-Jan-99 121.09
16-Jan-99 121.49
23-Jan-99 121.44
30-Jan-99 121.33
6-Feb-99 120.33
13-Feb-99 120.5
20-Feb-99 122.36
27-Feb-99 121.81

is there a macro that I could use to convert this data to monthly values by
averaging
all weekly values in jan and feb to give me 1 monthly value for Jan and 1
for Feb and so on for the entire data set. If this is posible could someone
please help me out with some Ideas. Also I want the finished monthly values
to apear in another column or sheet.

Any thoughts ??

Dan.