Charting - Dynamic Dates

[Redink]

Hi,
I have a single spreadsheet with dates in Column A and measurements in Column B. I am trying to create a chart with a dynamic date range from 5 years ago to YTD. When a user enters a new date and data, I would like the chart to display the added data, calculate what 5 years prior to this is, and then adjust the date range accordingly on the chart.

I have intermediate charting skills, but beginner VBA skills. Any suggestions?

Erin

 

[Rasm]

ok lets say you have a variable called Adate and diminsioned as a date

You should always declare your variable - so the first line in your code should be

Option Explicit

You can now extract the year as

YearX=Year(Adate)
DayX=Day(Adate)
Month=Month(Adate)

Now you can construct from this whatever date you want - Not sure if this helps

 

[Redink]

Thanks Rasm for the response. I haven't yet learned how to use code, so for now I'm using the tools Excel has given me.

I figured out to name the columns and then use an OFFSET equation to capture the most recent dates.

If anyone is looking to perform a similar task I found this article very helpful -
http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=518