I manually generate the same product activity report on a monthly basis. Each report consists of a product start date and a product end date. What I am trying to do is figure out a date formula that I can use in a macro.
The date formula needs to look at the current month and then refer to the product start date and product end date to determine, which month's data to include in a bar chart. The bar chart will only ever display 6 months (at most) worth of data (i.e. if we were only 4 months into a product then we'd only show 4 months of data).
For example, if the Product start date is 1/1/2016 and the end date is 12/31/2017, and the current month is August 2016, then I'd want my bar chart to display data for March 2016 - August 2016 (i.e. 6 months of data). The september report, would display the April through September data... effectively rolling along at 6 month increments.
The source data for this report is what the user would paste into another excel sheet. Once the source data is pasted, the idea is that the user would then click to run a macro, the macro would increment the current month's date from the previous reporting file, and then copy the respective month's data and paste it into the table accordingly, which would then be used in the bar chart.
The date formula needs to look at the current month and then refer to the product start date and product end date to determine, which month's data to include in a bar chart. The bar chart will only ever display 6 months (at most) worth of data (i.e. if we were only 4 months into a product then we'd only show 4 months of data).
For example, if the Product start date is 1/1/2016 and the end date is 12/31/2017, and the current month is August 2016, then I'd want my bar chart to display data for March 2016 - August 2016 (i.e. 6 months of data). The september report, would display the April through September data... effectively rolling along at 6 month increments.
The source data for this report is what the user would paste into another excel sheet. Once the source data is pasted, the idea is that the user would then click to run a macro, the macro would increment the current month's date from the previous reporting file, and then copy the respective month's data and paste it into the table accordingly, which would then be used in the bar chart.