find the next due date after today within a group in an Excel PivotTable
- by Dennis George
I have got a table set up in one sheet with "transactions". Each row contains a name of a vendor, the amount owed or paid depending on transaction type, and the due date/transaction date. Here is some simplified sample data:
Vendor Date Invoice Payment
Vendor A 6/30 $200
Vendor A 6/30 ($200)
Vendor B 7/5 $500
Vendor B 7/5 ($500)
Vendor C 10/28 $50
Vendor A 10/30 $100
Vendor C 11/15 $50
I have already built a PivotTable from that table to group these transactions by vendor and sum the remainder owed. What I'm trying to figure out is how to, for each vendor, get the next due date (min date of the group, excluding dates < Today()), or if there is no next due date then I want to see the max date for that group.
Here is what my PivotTable looks like, plus the date column I'd like to add (assuming Today() = 10/23):
Vendor Date Owed
Vendor B 7/5 -
Vendor C 10/28 $100
Vendor A 10/30 $100
I know calling it next due date might not be so accurate if I end up with the date of a payment in that column, but I'm ok with that.
tl;dr : I want to find the next earliest date within each group, or the last date. How do I do this?
