Dan,

try:

=INDIRECT("B"&INT((MONTH($D1)-1)/3)+1+(YEAR($D1)-1947)*4)

Sorry about delay in reply .... time difference ... I have to sleep sometime!

HTH

"Dan Thompson" wrote:

> Im not sure if you got my reply to your reply but your formula worked
> unfortunatly my data set is more than just one year I need to be able to
> convert
> the data for several years and when I use your formula for more than one year
> I end up with the same data for each year. in other words the values for
> 194701 100
> 194702 50
> 194703 200
> 194704 1000
>
> end up being the same for 1948 1949 ect..
>
> do you know how to fix this ?
>
> Thanks in Advance
> Dan Thompson
>
>
> "Toppers" wrote:
>
> > Dan,
> >
> > Try this:
> >
> > Assume your quarterly data is A1:B4.
> >
> > In (for example) d1, type 01/01/1947 (to use your year) and format as
> > date;in D2 downwards, type/copy down "=d1+7" until 31/12/1947 reached; this
> > will give weekly dates.
> >
> > In E1 put "=indirect("B" & int((Month(D1)-1)/3)+1)" and copy this down to
> > last non-blank cell of column D. This should give weekly data corresponding
> > to the quarterly figures.
> >
> > HTH
> >
> > "Dan Thompson" wrote:
> >
> > > I have been trying to write a module in vba that will convert a dataset that
> > > is quarterly data to a dataset that is a weekly data set, (as the data I have
> > > is only available in quarterly data format) but I would like to cross plot it
> > > against some different weekly data I have in a excel chart with a weekly date
> > > basline.
> > >
> > > Example...
> > >
> > > My quarterly data set looks somthing like this.
> > > YearQuarter Data
> > > 194701 100 <--Q1 Data
> > > 194702 50 <--Q2 Data
> > > 194703 200 <--Q3 Data
> > > 194704 1000 <--Q4 Data
> > >
> > > This is what it should be after conversion macro is run.
> > > 1-Jan-47 100 <-Start of Q1 Data
> > > 8-Jan-47 100
> > > 15-Jan-47 100
> > > 22-Jan-47 100
> > > 29-Jan-47 100
> > > 5-Feb-47 100
> > > 12-Feb-47 100
> > > 19-Feb-47 100
> > > 26-Feb-47 100
> > > 5-Mar-47 100
> > > 12-Mar-47 100
> > > 19-Mar-47 100
> > > 26-Mar-47 100
> > > 2-Apr-47 50 <-- Start of Q2 Data
> > > 9-Apr-47 50
> > > 16-Apr-47 50
> > > 23-Apr-47 50
> > > 30-Apr-47 50
> > > 7-May-47 50
> > > 14-May-47 50
> > > 21-May-47 50
> > > 28-May-47 50
> > > 4-Jun-47 50
> > > 11-Jun-47 50
> > > 18-Jun-47 50
> > > 25-Jun-47 50
> > > 2-Jul-47 200 <--Start of Q3 Data
> > > 9-Jul-47 200
> > > 16-Jul-47 200
> > > 23-Jul-47 200
> > > 30-Jul-47 200
> > > 6-Aug-47 200
> > > 13-Aug-47 200
> > > 20-Aug-47 200
> > > 27-Aug-47 200
> > > 3-Sep-47 200
> > > 10-Sep-47 200
> > > 17-Sep-47 200
> > > 24-Sep-47 200
> > > 1-Oct-47 1000 <-- Start of Q4 Data
> > > 8-Oct-47 1000
> > > 15-Oct-47 1000
> > > 22-Oct-47 1000
> > > 29-Oct-47 1000
> > > 5-Nov-47 1000
> > > 12-Nov-47 1000
> > > 19-Nov-47 1000
> > > 26-Nov-47 1000
> > > 3-Dec-47 1000
> > > 10-Dec-47 1000
> > > 17-Dec-47 1000
> > > 24-Dec-47 1000
> > > 31-Dec-47 1000
> > >