I am setting up a pivot table that will reveal the amount of expenses that are used up within a specified date range. The data table has a start date when the expense started to be used up and an End date from which the expense stated in the invoice concluded.
The idea of the pivot table is match expenses accrued over a time period. the time period would be specified by entering a start date and an end date. the Pivot table will look to the data page and show the actual expense used over the specified period - not what was paid for in the specified period which will be different.
Some of the fields include Start Date, End Date,
Days(between Start date & end date + 1)
Amount per day which is Net Amount divided by the number of Days.
The test data i have shows the total expense amounts correctly that are in the data table when filtered date ranges are not added ie StartDate & End Date are set to all. however when I enter a start date and end date only invoices that have the same start date & end date are included in the expense amount. For instance if I set the start date to July 1st and the end date to july 31st only the amounts of invoices with a start date of july 1 and an end date of July 31st are shown. So for instance an invoice that had services that were used up from July 5th to July 25th will not be included in the totals where a date range of July 1 to july 31st. As well a transaction that is used up from july 1st to August 31st will not be included in the expense totals displayed for a July 1 to july 31st date range whereas the proportion used up in July ie Amount per day multipled by 31 days(in july) should be included in the expense total for a date range of july 1st to July 31st.
My question is how do i get my Excel pivot table to see the 2 date filters as a pair with one being the start date and the other being the end date, and how do i get the Pivot table to include the part of an invoice that relates to the specified date range in the expense figures shown in the pivot table. Is there a way for the Pivot table to do this or do i need to write a VBA procedure to do this ie loop through each invoice, process invoices that meet the date condition and add each average daily amount to the respective expense category and then only display the accrued expenses for the specified date range. I have attached the file i have been trying to do this with
I tried to unpload a compressed file but it wouldn't do it.
|