Have csv credit card data of charges, each with a posted date. Have then manually added a custom category name for each charge. Pivot table will generate rows of expense sums for each category and displayed in columns by calendar month. I want the same except by a customized date range for each column.
MacBook Pro (M4)
> I would like the columns divided according to rules that would align the columns with my credit cart statement dates, which happen to be from the 10th of a month to the 9th of the following month.
Ahh, I see - and yes, filters wouldn't work for that.
Unfortunately, the standard Pivot Table features won't work either - they're based on calendar dates (years/ quarters/months) and not arbitrary dates.
The easiest solution is an additional column in your source table that maps to the statement the charge falls in to.
For example, I created a simple transactions table containing a transaction Date, Category and Amount.
To this, I inserted a new Column B to be used for the statement date:
The formula in B2 is:
=EDATE($A2,IF(DAY($A2)≤9,0,1))
EDATE() takes a date (in this case, the value in cell $A2) and adds an offset of months to determine a new date. I check the DAY() of the date and if it's ≤9, I add 0 (keep the current month), otherwise add 1 (it's billed on next month's statement.
Filling this down the column and formatting it to show Month and Year only, I now have a column that tracks which statement each transaction is billed to. You can incorporate this statement date into your Pivot Table rather than the raw transaction date:
And here are my Pivot Table settings:
Note that I've added Statement twice - one for 'Year' and once for 'Month', since transactions after December 10th of any year will fall into next year's statement. If I'd just used 'Month' it would calculate late-December charges in the 'January' row, merged with last year's expenses.
You could also use Statement once, with a 'Year-Month' setting and get a single list, but I prefer breaking out the years for visual purposes.
You can apply filters to your Pivot Table, just like on your main table.
Once you've structured your Pivot Table as desired, with the appropriate rows, columns and values, you can then click the Filter tab in the Inspector. Here you can add a filter on the Date column and specify any date range you want. The Pivot Table will then update with only the matching rows in the summary.
Is that sufficient?
Thanks.
My pivot table is constructed with only the Date field in the Columns list. Selecting the 'I' for that list item allows preconfigured calendar day selections like Value, Month, Quarter, Year-Month, etc.
I would like the columns divided according to rules that would align the columns with my credit cart statement dates, which happen to be from the 10th of a month to the 9th of the following month.
Is there a way to do that for the Pivot table?
custom date ranges for pivot table
