Get the date of every Monday for a rolling year

  • Thread starter Thread starter rocketD
  • Start date Start date
R

rocketD

Hi All,

I'm trying to plot weekly averages on a rolling window of one year.
The users are interested in the data averaged from Monday through
Sunday, and they want to view those weekly averages every Monday
morning. The averages are all calculated, but I can't figure out how
to get a rolling window of weeks. To be specific, today is Monday,
March 9, 2009. I want my chart to start Monday, March 10, 2008, and
show weekly averages through today. Next Monday, March 16th, 2009, I
want my chart to automatically start Monday, March 17, 2008, and show
the weekly averages through March 16, 2009.

Does anyone know if this is possible?

Dara
 
If A1 contains 9th March 2009 as an Excel date, then:

=A1-364

will give you 10th March 2008.

If this does not solve your problem, then perhaps you can post some
more details of what data you have and what you want to do to it (and
where).

Hope this helps.

Pete
 
That won't work if you have a 2/29 between the two dates. Better to
use:

=Date(year(A1),month(A1),day(A1-1))
 
That won't work if you have a 2/29 between the two dates.  Better to
use:

=Date(year(A1),month(A1),day(A1-1))





- Show quoted text -
Click to expand...

Sorry, I don't think I was clear enough. I figured it out, though.
The folks want to see averages every Monday, so that the weekly
average is Sunday through Monday. I have the worksheet set up to look
at the daily data of other worksheets, using a sumif command to
average data by week number (using the WEEKNUM() function). If I just
use Now()-365 or something of that nature, then it takes me back to
one year ago without regard to the Monday I'm interested in. To solve
for that, this is the formula I ended up using:

=$F$1-365-(WEEKDAY($F$1-365,2)-1)

Where $F$1 is a cell that refers to the max of a column of dates on
another worksheet within the workbook (it changes daily, as the
database is updated). I then allowed 5 rows above that in which I
decreased the date by 7 successively. So, if that formula above is in
cell E7, then I populated E6 through E2 with =E7-7, =E6-7, =E5-7,
=E4-7, =E3-7. That way, I have dates for Mondays up to 31 days prior
to the most recent date on which I have data. Likewise, I populated
the cells below cell E7 with =E7+7, =E8+7, etc. until I reached one
week after the most recent date for which we have data. That way,
I'll always have weekly averages, calculated Sunday through Monday,
for about a year previous to the most recent data.
 
Back
Top