Calculating 12 months back

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to Gather data from an existing worrk sheet to
use in a trend chart. I currently enter 2 dates as shown
below to gather data for a period but what I need is to be
able to take that end date and gather data from that date
back 12 calendar months. Ithink if I can get the headers
right (as shown below) I could then search my table and
gather the info I need to create a chart and show trends.
Is it possiblbe to ignore day and just use year and month?

Start date 1/1/2004 End date 2/29/2004


Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03

Nov-03 Dec-03 Jan-04 Feb-04
 
Hi
not really sure based on your example data but maybe (if A1 stores the
date)
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))-(MONTH(A1)=2)*(DAY(A1)=29))
 
Or, if you have the Analysis TookPak installed, you can obtain the end of a
month with the following:

=EOMONTH(start_date,months)

where start_date represents your starting date (not text, but a valid Excel
date value) and months is the number of months before (a negative number) or
after (positive number) start_date.

Search Excel help for more info on this function.

HTH
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh
 
I tried the formulas in the responses but ran into trouble
when I tried a sum product to find entries made during
those months. I've tried to explain below. Any input would
help please.

2/15/2004 (this is the search date entry.) lets say A1


(this is the Months backed off from the entry date month)
Aug-03 Sept-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04

? ? ? ? ? ? ?
( the ? are the number of entrys (on another worksheet)
made during the months that have been backed off of the
search date. I would hope to use the backed off months to
search the other worksheet and sum the amount of enrties
for each of the past months)
 
Maybe something like this

=SUMPRODUCT(--(A2:A30<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),B2:
B30)

will sum all entries in B2:B30 where A2:A30 is earlier than today minus a
year
 
The problem I have is that I can't just total the past 12
months. I need to total each of the last 12 month to get
12 different totals which I can then place in a chart to
show trend.
 
Back
Top