Thread: Days into Months/Year

I get the data by dates. (month, day, year).

1) a) I want to be able to take it to a pivot table and have the column by month within a year.
b) Also, how do I do the same function using the sumproduct function.


2) Once I have that data I want to be able to take it into a fixed (non-pivot table report) and plug in the info my month and year. the problem in this step is that the data does not contain always every single month so the pivot table does not have all the months and years.
Also, how do a link the month March 2008 to the fixed cell in my final report March 2008, if the pivot table is dynamic and I do not know in what cell the information will appear.

Hi

Here goes for some of it.

1a) create a new column that is the concatenation of the year and month, then use this in your pivot table
1b) =sumproduct(--(yearrange = year),--(monthrange = month),(datarange))
This is generic in form but can't give much more unless you give some specifics on your data structure.
2a) If I read this right, you could use possibly use HLOOKUP with perhaps MATCH to return your data from the pivot table. Again need more detail on your data and structure.
3) Either HLOOKUP or perhaps INDEX / MATCH.


rylo

Here is a short ex. data for:

#1) I want to be able to add the numbers that appear in the sales comlumn if the data column reads between january 1 2008 to january 31 2008. The result should equal "33". How can I make it work for SumProduct and SumIf. do you suggest another function?

Date Sales
1-Jan-07 10
15-jan-08 15
2-Jan-08 18
4-Apr-08 31

#2) To simply this second inquiry. How do export from a pivot table the results that belong to April, 2008 to a static specific cell outside the report?

Hi

can you please attach an example workbook for us to work on - saves us guessing at your structure, and saves us retyping / entering data.

rylo