| Pivot Table - Filtering with a date range with a start and end date |
| Terry Costello posted at Thursday, November 13, 2008 8:40 AM |
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.
|
 |
|
|
| |
| Pivot table filter with a date range |
| [)ia6l0 iii replied to Terry Costello at Thursday, November 13, 2008 9:09 AM |
| Hello Terry,
I could not see the attached file. Although i did not understand your question correctly (It was too big... :-)), i think what you are looking for is to set a Start and a End Date range to filter in a Pivot table.
If yes, then you can select the group by filter on the data column and then choose "Start" and "End Date" range on the data column.
Note: you need to have the "Group By" column set on it first. |
|
| |
| Pivot Table variable date start 7 Date end |
| Terry Costello replied at Friday, November 14, 2008 7:46 PM |
Hi Lalji
Thanks very much for your post.
I have attached the file which has both the data worksheet invoutstanding which contains
data used by the pivot table - ie Cell A1 to J20 . Columns K to V show the monthly totals that the pivot table should reveal if it is correctly only displaying the proportion of each exense used up for each day during the specified date period which is End Date plus 1 less start date. ie if the user types September 1st as the start date and September 30th as the end date expenses used up include $1366.45 which includes items that have a start and end date
within the Start date and end date ie Sept 11 to Sept 17th as well as items that start before e the start and or continue after the end date. the aim of the table is to work out how much expense has been accrued within tthe start and end dates inclusive.
The spreadsheet is located at
http://www.eggheadcafe.com/fileupload/-621222952_Copy of InvoicesToPayTest.zip
Regards and many thanks
terry
|
|
| |
| Hi Srinath |
| Terry Costello replied to [)ia6l0 iii at Saturday, November 15, 2008 7:30 AM |
What I'm looking at trying to do is to work out the cost of each invoice
over a period of time measured in days specified by the start date and end date inclusive
ie End date + 1 - Start Date
Each invoice(payable ie expense) line item has a start date which is the first date that the invoiced expense applies from through to the end date which is the last date that
invoiced expense applies from. the line also has an Amount per day field..
So for instance if an invoiced expense say Newspapers was $61 which covered the
period from April 1 to May 31st (61 days) was contained in the data worksheet The amount per day field would be $1 - ie $61 divided by 61 days - If i entered Start date on the pivot table page as April 1 and the End date as April 30
then newspaper expense for this period would be $30 ie Amt per day $1 multiplied by the date range which is 30 days inclusive of the start and end date. Maybe i need to have code that will create a variable such as ExpenseCostInPeriod which will
loop through each day from the start date to the end date and add the daily cost
to the variable. Once the end date is reached the expense and its ExpenseCostInPeriod
will be written to the pivot table report and then the same thing will happen to the next expense category. One complication could occur if there were multiple invoices for the same expense category. So far the only expenses that show in the pivot table are expenses that have the same start date and the same end date as the ones entered by the user on the pivot table report. this means that invoices that either cover only part of the date range or invoices which overlap ie cover a period of time before and or after the inputted date range will not be displayed int he pivot table report at all. I have attached the spreadsheet Sheet 5 contains the Pivot table and the other sheet contains the data A1 to I 20
with other information in other columns being test results showing what the result should be for each month if the start date of ie 1st of July to 31st of July etc is inputted into the start date and end date filters on the pivot table.
The aim of this pivot table is to enable me to quickly run a report which will allow me to calculate costs over the specified date range entered.
http://www.eggheadcafe.com/fileupload/-621222952_Copy of InvoicesToPayTest.zip
Once again thank you for your interest and your post.
|
|
| |
| Pivot Table variable date start & Date end |
| Terry Costello replied at Sunday, November 16, 2008 8:08 PM |
Hi Lalji
At present my Pivot table only adds transactions that have both the same start and end dates that are entered in the start date and end date filters. Therefore transactions that have expense accrued during the start and end date period but
1. Not starting on the start date
2. Not ending on the end date
3. occurring after the start date but before the end date
4. starting prior to the start date
5. ending after the end date
6. Starting before the start date and ending after the end date
All transactions of this nature are not included in the pivot table's reply to a particular start date and end date being set.
For instance September 2008 only 2 expenses Superannuation $700 and Telephone $138.13 resulting in total expenses being $838.13 being shown as expenses accruing between September 1 and September 30 inclusive (ie 30 Days ie End date + 1 less Start Date)
However in the file I have attached CopyOfInvoicesToPayTest The data
worksheet InvOutstanding contains 8 other expense items that are included in the month of September but have not been included in the pivot table report when the start date of September 1 and the end date of September 30 are entered on the pivot table sheet - Sheet 5 They are
Tot Amt
StartDt EndDate Days PerDay Sep 1 to 30
11/9 17/9 7 16.57 116
18/9 24/9 7 17.65 123.55
25/9 1/10 7 16.16 96.99
26/9 2/10 7 2.86 17.14
1/8 30/9 61 0.84 25.89
1/7 30/6/09 365 0.68 20.47
7/9 6/11 60 1.85 42.48
1/8 28/2/09 212 2.89 86.63
Instead of the total expense listed for September being 1366.45
only the two expenses with a start date of 1/9 and end date of 30/9
were listed which was $838.13.
To solve this problem the pivot table needs to add expenses that
meet the 6 criteria listed above. Does this need code or can
something be done to the properties of the pivot table filters so
that it will examine each start and end date and
1. Work out how many days of this expense fall within the date range
2. Multiply no of days in the date range by expense per day
3. If 1 expense category has muliple entry then sum these although
i think the Pivot table already does this.
I have attached the Pivot table file which has the worksheet invOutstanding
as the data worksheet and sheet5 as the Pivot table worksheet
at http://www.eggheadcafe.com/fileupload/-621222952_Copy of InvoicesToPayTest.zip
Once again thank you for replying to my earlier post.
Regards
Terry
|
|
| |
|
|